PDA

View Full Version : Downloading data from webpage into Excel



Elvis
04-04-2009, 06:36 AM
Hi,

Does anyone know whether it would be possible to download data directly into excel or as a txt/csv file from the following webpage?

http://www.dol.gov/opa/media/press/eta/ui/current.htm

Specfically I am only intrested in downloading the table under the section below.

Thanks,

Elvis

UNEMPLOYMENT INSURANCE DATA FOR REGULAR STATE PROGRAMS

WEEK ENDING
March 28
March 21
Change
March 14
Year

Initial Claims (SA)
669,000
657,000
+12,000
644,000
389,000
Initial Claims (NSA)
594,464
590,067
+4,397
601,193
341,846
4-Wk Moving Average (SA)
656,750
650,250
+6,500
650,000
367,750

Advance



Prior1
WEEK ENDING
March 21
March 14
Change
March 7
Year

Zack Barresse
04-04-2009, 10:31 AM
Hi there,

You could go by HTML table number if you wanted. Not sure how many tables you're wanting, but here are the first 5 tables via web query...

Sub GrabHTMLTable()

Dim wb As Workbook, ws As Worksheet
Dim objWeb As QueryTable, iRow As Long, strURL As String
Dim arrTables(1 To 5) As Byte, i As Long

arrTables(1) = 9: arrTables(2) = 10: arrTables(3) = 11
arrTables(4) = 12: arrTables(5) = 13
strURL = "http://www.dol.gov/opa/media/press/eta/ui/current.htm"
Set wb = ActiveWorkbook
Set ws = wb.Worksheets.Add(after:=wb.Worksheets(wb.Worksheets.Count))
iRow = 1
For i = LBound(arrTables) To UBound(arrTables)
Set objWeb = ws.QueryTables.Add(Connection:="URL;" & strURL, Destination:=ws.Cells(iRow, 1))
objWeb.WebSelectionType = xlSpecifiedTables
objWeb.WebTables = arrTables(i)
objWeb.Refresh BackgroundQuery:=False
objWeb.SaveData = True
iRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
Next i
ws.Cells.EntireColumn.AutoFit

End Sub

Let us know if that helps or not. It isn't too fast, because each table has its own web query.

HTH

victoruno
04-17-2009, 06:03 PM
or change
objWeb.WebSelectionType = xlSpecifiedTables

to

objWeb.WebSelectionType = xlEntirePage

for the ... "Entire Page" and then parse through it in Excel.