Web query

[Mahesh]

Hi,

I have created a web query that pulls the required data from the web site
from the page 1. But there are 100 pages and i need to collect the data from
all the 100 pages.following is the url for the same. If you see in the url
N=0 is the first page and N=12 will be the second page and multiples of 12.
How can i increment this and automate. Please advice.
URL;http://www.lowes.com/lowes/lkn?action=productList&N=0&Ntk=i_appliances&Ntt=GE", _

 

[Don Guillett]

See the difference below
http://www.lowes.com/lowes/lkn?action=productList&N=0&Ntk=i_appliances&Ntt=GE
http://www.lowes.com/lowes/lkn?action=productList&No=12&Ntt=GE&Ntk=i_appliances&N=0
http://www.lowes.com/lowes/lkn?action=productList&No=24&Ntt=GE&Ntk=i_appliances&N=0

so incorporate a looping macro with the variable inserted, something like:
for i = 1 to whatever number step 12

http://www.lowes.com/lowes/lkn?action=productList&No=" & i &
"&Ntt=GE&Ntk=i_appliances&N=0
do something with the info
next i

 

[Mahesh]

Hi Don,

The following is the code i have used to retrive the data. the one u have
suggested is not working. as you mentioned only for the first sheet the url
is different but from next onwards it is same except the increment no ie., N
= 12 and will increase like 12, 24, 36..etc. what i need is the data from
each and every page should get copied from the site and should be pasted one
below the other.

Private Sub CommandButton1_Click()
Dim objBK As Workbook
Dim objQT As QueryTable
Dim i As Integer
i = 36

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=" & i &
"12&Ntt=GE&Ntk=i_appliances&N=0", _
Destination:=.Range("A1"))

End With

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

'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

End With
End Sub

 

[Don Guillett]

You did not properly construct your loop with the step and the next i
There is another problem in that the first url is different than the
rest so that must be accounted for with an if statement or a list which is
not as good.

Who are you and what is this for?

http://www.lowes.com/lowes/lkn?action=productList&N=0&Ntk=i_appliances&Ntt=GE
1st one
http://www.lowes.com/lowes/lkn?action=productList&No=0&Ntt=GE&Ntk=i_appliances&N=1
no
http//www.lowes.com/lowes/lkn?action=productList&No=12&Ntt=GE&Ntk=i_appliances&N=0
http://www.lowes.com/lowes/lkn?action=productList&No=24&Ntt=GE&Ntk=i_appliances&N=0

 

[Mahesh]

HI Don,

I work for Genpact and i need this code to cut down my work which is
monotonous and time consuming. what i have taught is for the first url i will
write a seperate code and pull the data and for the rest i need it in a loop
which can pull the data and place in seperate worksheets or in the same work
sheet one under the other. This is very usefull to me and i am using from
last 2 years. Hope u wont disappoint me. Thanks for ur great help.

 

[Don Guillett]

This can be done with a if/end if in the table desired. What you want can be
done very neatly. Contact me privately.

 

[Mahesh]

HI don ,

Can i have send a mail to (e-mail address removed). Is this correct