I had to switch to method 2 to get each chemical webpage. Try this code.
Which data do you need? try using method one manually (Data - Import
External Data - New Web Query) with one of the webpages from the code below
and select one or more tables and see if the results are usable.

I will help as required.


Sub Getchemicals2()


Set ChemicalSht = Sheets.Add(after:=Sheets(Sheets.Count))
ChemicalSht.Name = "Chemicals"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URLFolder = _

"http://www.ilo.org/public/english/protection/safework/cis/products/icsc/dtasht/"
ChemicalRowCount = 1
For Letters = 0 To 25
AlphaLetter = Chr(Asc("a") + Letters)

URL = URLFolder & AlphaLetter & "_index.htm"

'get web page
IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

H2Found = False
For Each itm In IE.document.all
If H2Found = False Then
If itm.tagname = "H2" Then
H2Found = True
End If
Else

If itm.tagname = "A" Then
If itm.innertext = "" Then Exit For

'chemical name
ChemicalSht.Range("A" & ChemicalRowCount) = itm.innertext
'webpage
ChemicalSht.Range("B" & ChemicalRowCount) = itm.href

ChemicalRowCount = ChemicalRowCount + 1
End If
End If
Next itm

Next Letters


End Sub


"Joel" wrote:

Thhe code below was very simple. I did it in about 15 minutes. It gets the
names of all the chemicals. I will work on the rest later.


Sub Getchemicals()

Set TempSht = Sheets.Add(after:=Sheets(Sheets.Count))
TempSht.Name = "Temp"
Set ChemicalSht = Sheets.Add(after:=Sheets(Sheets.Count))
ChemicalSht.Name = "Summary"

URLFolder =
"URL;http://www.ilo.org/public/english/protection/safework/cis/products/icsc/dtasht/"
ChemicalRowCount = 1
For i = 0 To 25
AlphaLetter = Chr(Asc("a") + i)

TempSht.Cells.ClearContents

With TempSht.QueryTables.Add(Connection:= _
URLFolder & AlphaLetter & "_index.htm", _
Destination:=TempSht.Range("A1"))

.Name = "a_index"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

'move data from tempory sheet to chemical sheet
TempRowCount = 17
Do While Range("C" & TempRowCount) < ""
ChemicalSht.Range("A" & ChemicalRowCount) = _
TempSht.Range("C" & TempRowCount)

ChemicalRowCount = ChemicalRowCount + 1
TempRowCount = TempRowCount + 1
Loop
Next i

TempSht.Cells.ClearContents

End Sub


"Roger on Excel" wrote:

Hi Joel,

Thanks for responding.

The first approach would work best for me - i have over 1000 chemicals in my
list.

here is an example of the website i would like to gather specific data
tables from :

http://www.ilo.org/public/english/pr...5/icsc0553.htm

each chemical has a hyperlink to its own web page like this one.

Let me know what you think

Best regards,

Roger
----------------------------------

"Joel" wrote:

Ther are tow basic mathods that can be used.

1) Perform a web query. First setup a Reord Macro (Tools - Macro - Start
Recording).. then perform one query manually by going to Data - Import
External Data - New Web Query. Next modify the the recorded macro as
required to add a loop changing the chemicals and the destination location so
the data doesn't over-write each other.

If one above doesn't work

2) Open an Internet Explorer application in Excel. through the Internet
Explorer request each chemical and extract each results through the Internet
Explorer Application.

Let me know which approach you want to use. I can help with both approaches.

"Roger on Excel" wrote:

I use hyperlinks for chemicals that access a webpage for the individual
compounds showing tables of data for that chemical.

I have a large list of chemicals and I want to automate the downloading of
specific data in the tables from the web pages.

The chemical data is stored in the same format of tables on each webpage.

Can anyone help?

Thanks,

Roger