Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from web
i have this link site http://www.capitalmarket.com/bulletin/bsebhav.asp?var=A In this site are present many data in table-format. Each page is identify from a letter from A to Z I would want to import for each letter site the value present in the related page and put the data into a sheet... My idea .... but only this i am not an expert: Put into a sheet SERVICE into column A yhe letter from A to Z and cicling with a for next the url: http://www.capitalmarket.com/bulletin/bsebhav.asp?var= & letter_present_in_ column_A_of_sheet_SERVICE and put the data into sheet TABLE Can you help me? Please very important for me. TIA. -- sal21 ------------------------------------------------------------------------ sal21's Profile: http://www.excelforum.com/member.php...fo&userid=2040 View this thread: http://www.excelforum.com/showthread...hreadid=377874 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from web
Hello Sal21, What I understand from your post is, you have to worksheets "Service and "Table". On the "Service" worksheet you want to have all 2 hyperlinks (URLs underlined in blue) from A to Z. What I don' understand is what you want to do with "Table" ? Let me know what eac sheet is for and I can help you write the code. Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=37787 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from web
Leith Ross Wrote: Hello Sal21, What I understand from your post is, you have to worksheets "Service and "Table". On the "Service" worksheet you want to have all 2 hyperlinks (URLs underlined in blue) from A to Z. What I don' understand is what you want to do with "Table" ? Let me know what eac sheet is for and I can help you write the code. Sincerely, Leith Ross Hi, Leith Ross for first tks for fast reply. Sorry, naturally for my english! Explain: My idea... In the SERVICE sheet insert in the column A the letter A, B, C, D.. (are the suffix for each page of the site) In the TABLE sheet insert the data getted from web.... in other word... For i 1 to 25 (cicling the column A) for i = 1 the first value of the cicle is A concatenate with the tipical VB simbol & the link: http://www.capitalmarket.com/bulletin/bsebhav.asp?var= & A Get the dat from this link and insert into sheet TABLE for i = 2 the first value of the cicle is B concatenate with the tipical VB simbol & the link: http://www.capitalmarket.com/bulletin/bsebhav.asp?var= & B Get the dat from this link and insert into exixstis old data gette from the link A into sheet TABLE ecc...... The structuture of the sheet is attached Hope i ma clear Tks for all +------------------------------------------------------------------- |Filename: GET_QUOTE.zip |Download: http://www.excelforum.com/attachment.php?postid=3473 +------------------------------------------------------------------- -- sal2 ----------------------------------------------------------------------- sal21's Profile: http://www.excelforum.com/member.php...nfo&userid=204 View this thread: http://www.excelforum.com/showthread.php?threadid=37787 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from web
Hello Sal21 , Hello Leith I don't think that is the best way but you may try this macro to import tables from all Web Pages test With Excel2002 & WinXP, 4 minutes to run the 26 WebPages Sub Importer_tableauPageWeb() 'MichelXld le 09.06.2005 ' 'activate Microsoft HTML Objects Library 'activate Microsoft Internet Controls ' Dim IE As InternetExplorer Dim maPageHtml As HTMLDocument Dim Htable As IHTMLElementCollection Dim maTable As IHTMLTable Dim j As Integer, i As Integer, x As Integer, Ligne As Integer Dim NbPages As Byte Application.ScreenUpdating = False For NbPages = 65 To 90 Ligne = 0 Sheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = Chr(NbPages) Set IE = CreateObject("InternetExplorer.Application") IE.Visible = False IE.navigate "http://www.capitalmarket.com/bulletin/bsebhav.asp?var=" & Chr(NbPages) Do Until IE.readyState = READYSTATE_COMPLETE DoEvents Loop Set maPageHtml = IE.document Set Htable = maPageHtml.getElementsByTagName("table") 'objet type table For x = 2 To Htable.Length - 1 Ligne = Ligne + i + 1 Set maTable = Htable(x) For i = 1 To maTable.Rows.Length 'each row in table For j = 1 To maTable.Rows(i - 1).Cells.Length 'each cell in row Cells(Ligne + i, j) = maTable.Rows(i - 1).Cells(j - 1).innerText Next j Next i Next x DoEvents Set IE = Nothing Next NbPages Application.ScreenUpdating = True End Sub Regards , michel -- michelxld ------------------------------------------------------------------------ michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367 View this thread: http://www.excelforum.com/showthread...hreadid=377874 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from web
michelxld Wrote: Hello Sal21 , Hello Leith I don't think that is the best way but you may try this macro to import tables from all Web Pages test With Excel2002 & WinXP, 4 minutes to run the 26 WebPages Sub Importer_tableauPageWeb() 'MichelXld le 09.06.2005 ' 'activate Microsoft HTML Objects Library 'activate Microsoft Internet Controls ' Dim IE As InternetExplorer Dim maPageHtml As HTMLDocument Dim Htable As IHTMLElementCollection Dim maTable As IHTMLTable Dim j As Integer, i As Integer, x As Integer, Ligne As Integer Dim NbPages As Byte Application.ScreenUpdating = False For NbPages = 65 To 90 Ligne = 0 Sheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = Chr(NbPages) Set IE = CreateObject("InternetExplorer.Application") IE.Visible = False IE.navigate "http://www.capitalmarket.com/bulletin/bsebhav.asp?var=" & Chr(NbPages) Do Until IE.readyState = READYSTATE_COMPLETE DoEvents Loop Set maPageHtml = IE.document Set Htable = maPageHtml.getElementsByTagName("table") 'objet type table For x = 2 To Htable.Length - 1 Ligne = Ligne + i + 1 Set maTable = Htable(x) For i = 1 To maTable.Rows.Length 'each row in table For j = 1 To maTable.Rows(i - 1).Cells.Length 'each cell in row Cells(Ligne + i, j) = maTable.Rows(i - 1).Cells(j - 1).innerText Next j Next i Next x DoEvents Set IE = Nothing Next NbPages Application.ScreenUpdating = True End Sub Regards , michel Hi Michel, no have dubt YOU ARE A WIZARD AND TKS FOR PATIENCE... .... The macro work fine Only one... For me not is important to import for each page the data into a different sheet... Page A into sheet A, page B into sheet B ecc..... Important is to insert all records only in one sheet for example into a sheet named TABELLA. Not is important to import also the title of record is the same for each page. Start the import of record from A2.. Is possible this modify? Tks and "kiss" from Napoli -- sal21 ------------------------------------------------------------------------ sal21's Profile: http://www.excelforum.com/member.php...fo&userid=2040 View this thread: http://www.excelforum.com/showthread...hreadid=377874 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from web
Hello Sal21 I hope this help you Sub Importer_tableauPageWeb_V02() 'MichelXld le 09.06.2005 ' 'activate Microsoft HTML Objects Library 'activate Microsoft Internet Controls ' Dim IE As InternetExplorer Dim maPageHtml As HTMLDocument Dim Htable As IHTMLElementCollection Dim maTable As IHTMLTable Dim j As Integer, i As Integer, x As Integer, Ligne As Integer Dim NbPages As Byte, y As Byte Application.ScreenUpdating = False For NbPages = 65 To 90 Set IE = CreateObject("InternetExplorer.Application") IE.Visible = False IE.navigate "http://www.capitalmarket.com/bulletin/bsebhav.asp?var=" & Chr(NbPages) Do Until IE.readyState = READYSTATE_COMPLETE DoEvents Loop Set maPageHtml = IE.document Set Htable = maPageHtml.getElementsByTagName("table") 'objet type table For x = 2 To Htable.Length - 1 If x = 2 And NbPages = 65 Then y = 1 Else y = 4 End If Set maTable = Htable(x) For i = y To maTable.Rows.Length 'rows in table Ligne = Ligne + 1 For j = 1 To maTable.Rows(i - 1).Cells.Length 'each cell in row Cells(Ligne, j) = maTable.Rows(i - 1).Cells(j - 1).innerText Next j Next i Next x DoEvents IE.Quit Set IE = Nothing Next NbPages Application.ScreenUpdating = True End Sub Regards , michel -- michelxld ------------------------------------------------------------------------ michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367 View this thread: http://www.excelforum.com/showthread...hreadid=377874 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
import from web
michelxld Wrote: Hello Sal21 I hope this help you Sub Importer_tableauPageWeb_V02() 'MichelXld le 09.06.2005 ' 'activate Microsoft HTML Objects Library 'activate Microsoft Internet Controls ' Dim IE As InternetExplorer Dim maPageHtml As HTMLDocument Dim Htable As IHTMLElementCollection Dim maTable As IHTMLTable Dim j As Integer, i As Integer, x As Integer, Ligne As Integer Dim NbPages As Byte, y As Byte Application.ScreenUpdating = False For NbPages = 65 To 90 Set IE = CreateObject("InternetExplorer.Application") IE.Visible = False IE.navigate "http://www.capitalmarket.com/bulletin/bsebhav.asp?var=" & Chr(NbPages) Do Until IE.readyState = READYSTATE_COMPLETE DoEvents Loop Set maPageHtml = IE.document Set Htable = maPageHtml.getElementsByTagName("table") 'objet type table For x = 2 To Htable.Length - 1 If x = 2 And NbPages = 65 Then y = 1 Else y = 4 End If Set maTable = Htable(x) For i = y To maTable.Rows.Length 'rows in table Ligne = Ligne + 1 For j = 1 To maTable.Rows(i - 1).Cells.Length 'each cell in row Cells(Ligne, j) = maTable.Rows(i - 1).Cells(j - 1).innerText Next j Next i Next x DoEvents IE.Quit Set IE = Nothing Next NbPages Application.ScreenUpdating = True End Sub Regards , michel Hi michelxld, tks many tks the code work fine.... Sal. -- sal21 ------------------------------------------------------------------------ sal21's Profile: http://www.excelforum.com/member.php...fo&userid=2040 View this thread: http://www.excelforum.com/showthread...hreadid=377874 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import txt with VB | New Users to Excel | |||
import XML data...Is there a size limit on the import? | Excel Discussion (Misc queries) | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
XML Import | Excel Worksheet Functions | |||
I can import Access Tables. But, I can't import Access queries | Excel Programming |