View Full Version : VBA script to download data from a url

07-22-2008, 06:16 AM
Can anyone provide me a VBA script that downloads data from a url into a specified spreadsheet ?

07-22-2008, 06:52 AM
Have you tried doing a search for this topic? It seems like it keeps coming up over and over...

07-22-2008, 06:05 PM
I just asked the same question a week or two ago. Solved it almost immediately after I posted my request. I posted the code I used to pull the data. Obviously you'll have to change the URL, but you'll also have to change the web table number in the code. I'm a VBA newbie, so I can't offer much help in how the tables are numbered on a website, but you can search this site for help.

Sub Macro1()

'Pull & post energy data from website

Dim sTS As String

sTS = Range("a1").Value
With ActiveSheet.QueryTables.Add(Connection:= _
& sTS, Destination:=Range("A5"))
.Name = "Energy Data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "8"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub