| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web query
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 ", _ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query
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...plianc es&N=0 http://www.lowes.com/lowes/lkn?actio...plianc es&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 "Mahesh" wrote in message ... 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 ", _ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query
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...pliance s&N=1 no http//www.lowes.com/lowes/lkn?action=productList&No=12&Ntt=GE&Ntk=i_applianc es&N=0 http://www.lowes.com/lowes/lkn?actio...plianc es&N=0 -- Don Guillett SalesAid Software "Mahesh" wrote in message ... 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...plianc es&N=0 http://www.lowes.com/lowes/lkn?actio...plianc es&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 "Mahesh" wrote in message ... 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 ", _ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query
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...pliance s&N=1 no http//www.lowes.com/lowes/lkn?action=productList&No=12&Ntt=GE&Ntk=i_applianc es&N=0 http://www.lowes.com/lowes/lkn?actio...plianc es&N=0 -- Don Guillett SalesAid Software "Mahesh" wrote in message ... 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...plianc es&N=0 http://www.lowes.com/lowes/lkn?actio...plianc es&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 "Mahesh" wrote in message ... 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 ", _ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Web query
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 "Mahesh" wrote in message ... 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...pliance s&N=1 no http//www.lowes.com/lowes/lkn?action=productList&No=12&Ntt=GE&Ntk=i_applianc es&N=0 http://www.lowes.com/lowes/lkn?actio...plianc es&N=0 -- Don Guillett SalesAid Software "Mahesh" wrote in message ... 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...plianc es&N=0 http://www.lowes.com/lowes/lkn?actio...plianc es&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 "Mahesh" wrote in message ... 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 ", _ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Import New Database Query (Union Query) in Spreadsheet | Excel Discussion (Misc queries) | |||
| Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
| Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
| Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
| How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Hybrid Mode
