Web Query
 

Hi,

I have used the following web query which downloads some data into my excel
sheet. Actually there are nearly 100 pages. I have written the code for 2
pages of data that will download to my excel worksheet. Is there is a way
where i can loop this to extract entire data from all the sheets of the web
site.



Sub RatesWebQuery()
Dim objBK As Workbook
Dim objQT As QueryTable
Dim ObjQT1 As QueryTable

Set objBK = Workbooks.Add

'Create query table to hold the rates.
With objBK.Worksheets(1)
Set objQT = .QueryTables.Add( _

Connection:="URL;http://www.lowes.com/lowes/lkn?action=productList&No=12&Ntt=GE&Ntk=i_applianc es&N=0", _
Destination:=.Range("A1"))
Set ObjQT1 = .QueryTables.Add( _

Connection:="URL;http://www.lowes.com/lowes/lkn?action=productList&No=24&Ntt=GE&Ntk=i_applianc es&N=0", _
Destination:=.Range("A25"))
End With

'Set QueryTable properties.
With objQT
.Name = "USD"

'Don't recognize dates.
.WebDisableDateRecognition = True

'Don't refresh query when file opened.
.RefreshOnFileOpen = False

'Ignore page formatting.
.WebFormatting = xlWebFormattingNone

'Wait for query to finish before continuing.
.BackgroundQuery = True

'Select a specific table.
.WebSelectionType = xlSpecifiedTables

'Import the table containing the data tables.
.WebTables = "15"

'Save the query with workbook.
.SaveData = True

'Adjust columns to fit the data.
.AdjustColumnWidth = True
End With
With ObjQT1
.Name = "USD"

'Don't recognize dates.
.WebDisableDateRecognition = True

'Don't refresh query when file opened.
.RefreshOnFileOpen = False

'Ignore page formatting.
.WebFormatting = xlWebFormattingNone

'Wait for query to finish before continuing.
.BackgroundQuery = True

'Select a specific table.
.WebSelectionType = xlSpecifiedTables

'Import the table containing the data tables.
.WebTables = "15"

'Save the query with workbook.
.SaveData = True

'Adjust columns to fit the data.
.AdjustColumnWidth = True
End With
With Application

On Error Resume Next

'Execute query and wait for it to finish.
objQT.Refresh BackgroundQuery:=False
ObjQT1.Refresh BackgroundQuery:=False
End With

End Sub