import from web

[sal21]

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.

[Leith Ross]

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 26 hyperlinks (URLs underlined in blue) from A to Z. What I don't understand is what you want to do with "Table" ? Let me know what each sheet is for and I can help you write the code.

Sincerely,
Leith Ross

[sal21]

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 26 hyperlinks (URLs underlined in blue) from A to Z. What I don't understand is what you want to do with "Table" ? Let me know what each 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 getted from the link A into sheet TABLE

ecc......

The structuture of the sheet is attached
Hope i ma clear
Tks for all.

[michelxld]

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

[sal21]

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

[michelxld]

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

[sal21]

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.