How to automate a web query

[RichardK]

Hi, i am new to the forum and my VB skills are limited to really what i can successfully do when i use a Macro Recorder. That said i am trying to automate a web query and import data into a spreadsheet.

I have a list of URL's in a spreadsheet - its weather data - and i want to import that data into an excel worksheet...advance about 300 rows and repeat that for all the URL's in the list [there are several hundred]

the problem is that the web query is not looking at the list so i literally import the same data over and over...thats the part i need help with. THis is what i have so far...please help

Sub attempt1()
'
' attempt1 Macro
'


'
Do Until IsEmpty(ActiveCell)
ActiveCell.Select


Selection.Copy
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"http://www.wunderground.com/history/airport/KASW/2014/8/31/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA&MR=1"

Sheets("Sheet1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.wunderground.com/history/airport/KASW/2014/8/31/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA&MR=1" _
, Destination:=ActiveCell)
.Name = "DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA&MR=1"
.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
ActiveCell.Offset(300).Select
Sheets("Web page").Select
ActiveCell.Offset(1).Select

Loop

End Sub

 

[RichardK]

or at least point me in a general direction...anyone?

 

[John_w]

Try the code at http://www.mrexcel.com/forum/excel-questions/748891-data-extraction.html#post3682308.

It expects the list of URLs to be in Sheet1 column A starting at A1. It imports the web data into Sheet2. It should work for your scenario with little, if any, modification.

 

[RichardK]

awesome - thanks for the pointer! I will play with this and let you know

 

[RichardK]

that worked amazingly well! THANKS!