Solved: Need Macro to pull data from Web

[jmarkc]

Hello. I am completely new to VBA. I have a very basic understanding that has been garnered through trial & error and by reviewing threads posted here. As such, I am in need of some help!

I am interested in creating a Macro that will enable me to go to a specific website (//tonto.eia.doe.gov/cfapps/STEO_Query/steotables.cfm?tableNumber=8&periodType=Monthly&startYear=2008&startMonth=1&startMonthChanged=false&startQuarterChanged=false&endYear=2008&endMonth=12&endMonthChanged=false&endQuarterChanged=false&loadAction=Apply+Changes)
pull data from a table that I have applied changes to and insert that data into an excel spreadsheet. The alternative would be to download an excel file found on that site. From there I think I can create a macro to parse the info to give me exactly what I need.

I have spent much time reviewing previous posts and attempting to modify the code given there, to no avail. What I believe is causing me the problems is that the table is not written in html. It is in java script.

I have attempted to do web queries, but because of the language, it does not retrieve the data.

Can anyone help please?! I have no code to offer as a starting point, but would be much grateful if someone could provide some!

[jmarkc]

Discovered that all I needed to do was put in the correct table number. Not sure what the ".Name" field in the code below means, but it doesn't affect the data. Just wanted to provide the solution:




Sub Macro1()
Dim sTS As String

sTS = Range("a1").Value
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;url posted here" _
& sTS, Destination:=Range("A3"))
.Name = "finance?fstype=ii&q=" & sTS
.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

[RonMcK]

jmarkc,

Suggestion: when you paste code, first, click the 'VBA' button, then, paste your code between the vba tags. The code will keep its formatting making it more readable when it is displayed.

Cheers!

[jmarkc]

Cool. Thanks. Will do. Wondered why it looked different than everyone elses!:beerchug: