import from web (2000 rs 1)

Topic

New Reply

When I try to run a web query on the URL you provided, Excel (2002 SP-3) refuses to import any data. Do others experience the same?

Reply | Quote

Replies

yes, I experience the same – “this query returned no data…” – same excel as you – 2002 sp3

Reply | Quote

http://www.capitalmarket.com/bulletin/bsebhav.asp?var=A

i have this link site
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 cycling 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.

Reply | Quote

Hans

same answer as Brooke (XL 2002). I get this error message:

Reply | Quote

I got the alphabet.

Excel 2000 SP3

Reply | Quote

The page Sal refers to contains many tables; you imported a table with navigation information, but none of the tables with data. It seems this web page doesn’t want the data to be imported into Excel.

Reply | Quote

Hans and other friends…
Look here:
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

Lie this code?

Reply | Quote

Glad someone else was able to help you!

Reply | Quote

Ok, finaly i have a code…
But not is wat i want;-(

1) insert in only one sheet named TABELLA all record refered from each lettere of link.
2) insert the records start from A2
3) insert into A1 the name of first record, into B1 the name of the second records…
4) not import a blank line, and insert up the record of the variuos letter link

Ony this little modify;-)

Reply | Quote

I don’t understand 3) insert into A1 the name of first record, into B1 the name of the second records…
What do you mean by that?

Reply | Quote

… sorry for english.

The name of the records:

COMPANY NAME (A1)
OPEN (B1)
….

Ops…
Naturally not import the line of each page with: BSE INDEX (not is importat in my case)

Reply | Quote

Sal,

Here is a variation on Michel’s code that imports everything into the TABLE worksheet. After the import, you must run another macro that removes the lines you don’t want. We have given you lots of macros that do similar things in the past, you should be able to write this one yourself.

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
Dim ws As Worksheet

Application.ScreenUpdating = False
Set ws = Worksheets(“TABLE”)
Ligne = ws.Range(“A65536”).End(xlUp).Row

For NbPages = 65 To 90
i = 1
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
ws.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

Reply | Quote