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?actio...liances&Ntt=GE
http://www.lowes.com/lowes/lkn?actio...appliances&N=0
http://www.lowes.com/lowes/lkn?actio...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
--
Don Guillett
SalesAid Software
[email protected]
"Mahesh" <[email protected]> wrote in message
news:[email protected]...
> 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",
> _

[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" wrote:

> See the difference below
> http://www.lowes.com/lowes/lkn?actio...liances&Ntt=GE
> http://www.lowes.com/lowes/lkn?actio...appliances&N=0
> http://www.lowes.com/lowes/lkn?actio...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
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Mahesh" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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?actio...liances&Ntt=GE
1st one
http://www.lowes.com/lowes/lkn?actio...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?actio...appliances&N=0


--
Don Guillett
SalesAid Software
[email protected]
"Mahesh" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> See the difference below
>> http://www.lowes.com/lowes/lkn?actio...liances&Ntt=GE
>> http://www.lowes.com/lowes/lkn?actio...appliances&N=0
>> http://www.lowes.com/lowes/lkn?actio...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
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Mahesh" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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",
>> > _
>>
>>
>>

[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" wrote:

> 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?actio...liances&Ntt=GE
> 1st one
> http://www.lowes.com/lowes/lkn?actio...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?actio...appliances&N=0
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Mahesh" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> >> See the difference below
> >> http://www.lowes.com/lowes/lkn?actio...liances&Ntt=GE
> >> http://www.lowes.com/lowes/lkn?actio...appliances&N=0
> >> http://www.lowes.com/lowes/lkn?actio...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
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> [email protected]
> >> "Mahesh" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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]

This can be done with a if/end if in the table desired. What you want can be
done very neatly. Contact me privately.
--
Don Guillett
SalesAid Software
[email protected]
"Mahesh" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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?actio...liances&Ntt=GE
>> 1st one
>>
>> http://www.lowes.com/lowes/lkn?actio...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?actio...appliances&N=0
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "Mahesh" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" wrote:
>> >
>> >> See the difference below
>> >> http://www.lowes.com/lowes/lkn?actio...liances&Ntt=GE
>> >> http://www.lowes.com/lowes/lkn?actio...appliances&N=0
>> >> http://www.lowes.com/lowes/lkn?actio...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
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> [email protected]
>> >> "Mahesh" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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",
>> >> > _
>> >>
>> >>
>> >>
>>
>>
>>

[Mahesh]

HI don ,

Can i have send a mail to [email protected]. Is this correct

"Don Guillett" wrote:

> This can be done with a if/end if in the table desired. What you want can be
> done very neatly. Contact me privately.
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Mahesh" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> >> 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?actio...liances&Ntt=GE
> >> 1st one
> >>
> >> http://www.lowes.com/lowes/lkn?actio...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?actio...appliances&N=0
> >>
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> [email protected]
> >> "Mahesh" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" wrote:
> >> >
> >> >> See the difference below
> >> >> http://www.lowes.com/lowes/lkn?actio...liances&Ntt=GE
> >> >> http://www.lowes.com/lowes/lkn?actio...appliances&N=0
> >> >> http://www.lowes.com/lowes/lkn?actio...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
> >> >> --
> >> >> Don Guillett
> >> >> SalesAid Software
> >> >> [email protected]
> >> >> "Mahesh" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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",
> >> >> > _
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>