Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in VBA
Thanks to all of you who have assisted me learn more about VBA through
this News Group - it is working for me slowly ...! In short, I have created a 20 or so line long VBA script which works fine for me sometimes. It gets the latest residential real estate data for Japan's 5 major cities and copies the relevant information straight into an Excel WorkSheet for me. The code starts with "Sub Queries()" and ends with "End Sub", but it generates some loops within the code depending upon how many different web pages it needs to visit each time. The number of web pages depends upon the number of available properties in each city whenever I choose to Run the VBA Macro. However, sometimes there are so many properties that I reach the last line of the destination WorkSheet and it stops short of what I want...! Can one of you kind folk please tell me what I have write into the script to make it automatically open a new destination WorkSheet and continue copying the data when the current WorkSheet reaches 64,000 lines, please. Also, can you tell where in the code I need to put this VBA command, please? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in VBA
Without seeing the code, I would have to speculate how you are doing this.
You probably have a helper sheet which collects each query one at a time and from there you copy it to a destination sheet. You must be keeping track of the LastRow on the destination sheet so you will know where to place the next query below the last (ie., LastRow = LastRow+2) to insert a blank row between queries. At that point check to see if LastRow 64000. If it is, add a new sheet and re-set the LastRow variable. If LastRow 64000 Then Worksheets.Add LastRow = 1 End If Also a tip to prevent file bloating with old Web Query names is to delete the helper sheet at the end of the queries, and of course add a new helper sheet at the start of the queries. Thousands of old web query names will be embedded on that sheet which will continually add to the file size until that sheet is deleted. Mike F "Pelham" wrote in message ups.com... Thanks to all of you who have assisted me learn more about VBA through this News Group - it is working for me slowly ...! In short, I have created a 20 or so line long VBA script which works fine for me sometimes. It gets the latest residential real estate data for Japan's 5 major cities and copies the relevant information straight into an Excel WorkSheet for me. The code starts with "Sub Queries()" and ends with "End Sub", but it generates some loops within the code depending upon how many different web pages it needs to visit each time. The number of web pages depends upon the number of available properties in each city whenever I choose to Run the VBA Macro. However, sometimes there are so many properties that I reach the last line of the destination WorkSheet and it stops short of what I want...! Can one of you kind folk please tell me what I have write into the script to make it automatically open a new destination WorkSheet and continue copying the data when the current WorkSheet reaches 64,000 lines, please. Also, can you tell where in the code I need to put this VBA command, please? Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in VBA
Dear Mike and Don
Thanks - very good of you! Here is the VBA code which keeps looping depending upon how large 'm' is. In this case 'm' is 250 (I need to enter this each time) which means the destination WorkSheet will be larger than 64,000 lines because each time 272 lines are copied. Since I do not use the term LastRow, what should I do? Sub Queries() ' ' Queries Macro ' Macro recorded 23/08/2006 by CBRE ' ' Keyboard Shortcut: Ctrl+Shift+Q ' For m = 1 To 250 With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.chintai.net/chintai/shuto/CGI/area_list.cgi?area=shuto_tokyo&city=1190_1200_1210 _1220_1230&s=0000000011111100000001000000003200000 000000000000000000010000000000220&p=" & m _ , Destination:=Range("A" & m + (271 * (m - 1)))) .Name = _ "area_list.cgi?area=shuto_tokyo&city=1190_1200_121 0_1220_1230&s=000000001111110000000100000000320000 0000000000000000000010000000000220&p=" & m .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "12" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Next m End Sub Regards Pelham Don Guillett wrote: As always, post your code for comments. You might want if lastrow64000 then destination sheet="sheet3" -- Don Guillett SalesAid Software "Pelham" wrote in message ups.com... Thanks to all of you who have assisted me learn more about VBA through this News Group - it is working for me slowly ...! In short, I have created a 20 or so line long VBA script which works fine for me sometimes. It gets the latest residential real estate data for Japan's 5 major cities and copies the relevant information straight into an Excel WorkSheet for me. The code starts with "Sub Queries()" and ends with "End Sub", but it generates some loops within the code depending upon how many different web pages it needs to visit each time. The number of web pages depends upon the number of available properties in each city whenever I choose to Run the VBA Macro. However, sometimes there are so many properties that I reach the last line of the destination WorkSheet and it stops short of what I want...! Can one of you kind folk please tell me what I have write into the script to make it automatically open a new destination WorkSheet and continue copying the data when the current WorkSheet reaches 64,000 lines, please. Also, can you tell where in the code I need to put this VBA command, please? Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in VBA
Dear Mike and Don
Thanks - very good of you! Here is the VBA code which keeps looping depending upon how large 'm' is. In this case 'm' is 250 (I need to enter this each time) which means the destination WorkSheet will be larger than 64,000 lines because each time 272 lines are copied. Since I do not use the term LastRow, what should I do? Sub Queries() ' ' Queries Macro ' Macro recorded 23/08/2006 by CBRE ' ' Keyboard Shortcut: Ctrl+Shift+Q ' For m = 1 To 250 With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.chintai.net/chintai/shuto/CGI/area_list.cgi?area=shuto_tokyo&city=1190_1200_1210 _1220_1230&s=0000000011111100000001000000003200000 000000000000000000010000000000220&p=" & m _ , Destination:=Range("A" & m + (271 * (m - 1)))) .Name = _ "area_list.cgi?area=shuto_tokyo&city=1190_1200_121 0_1220_1230&s=000000001111110000000100000000320000 0000000000000000000010000000000220&p=" & m .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "12" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Next m End Sub Regards Pelham Don Guillett wrote: As always, post your code for comments. You might want if lastrow64000 then destination sheet="sheet3" -- Don Guillett SalesAid Software "Pelham" wrote in message ups.com... Thanks to all of you who have assisted me learn more about VBA through this News Group - it is working for me slowly ...! In short, I have created a 20 or so line long VBA script which works fine for me sometimes. It gets the latest residential real estate data for Japan's 5 major cities and copies the relevant information straight into an Excel WorkSheet for me. The code starts with "Sub Queries()" and ends with "End Sub", but it generates some loops within the code depending upon how many different web pages it needs to visit each time. The number of web pages depends upon the number of available properties in each city whenever I choose to Run the VBA Macro. However, sometimes there are so many properties that I reach the last line of the destination WorkSheet and it stops short of what I want...! Can one of you kind folk please tell me what I have write into the script to make it automatically open a new destination WorkSheet and continue copying the data when the current WorkSheet reaches 64,000 lines, please. Also, can you tell where in the code I need to put this VBA command, please? Thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries in VBA
Try this:
Sub Queries() ' ' Queries Macro ' Macro recorded 23/08/2006 by CBRE ' ' Keyboard Shortcut: Ctrl+Shift+Q ' Dim m As Long Dim LRow As Long For m = 1 To 250 LRow = m + (271 * (m - 1)) If LRow 64000 Then Worksheets.Add LRow = 1 End If With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.chintai.net/chintai/shuto/CGI/area_list.cgi?area=shuto_tokyo&city=1190_1200_1210 _1220_1230&s=0000000011111100000001000000003200000 000000000000000000010000000000220&p=" & m _ , Destination:=Range("A" & LRow)) .Name = _ "area_list.cgi?area=shuto_tokyo&city=1190_1200_121 0_1220_1230&s=000000001111110000000100000000320000 0000000000000000000010000000000220&p=" & m .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "12" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False ' .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Next m End Sub Mike F "Pelham" wrote in message ups.com... Dear Mike and Don Thanks - very good of you! Here is the VBA code which keeps looping depending upon how large 'm' is. In this case 'm' is 250 (I need to enter this each time) which means the destination WorkSheet will be larger than 64,000 lines because each time 272 lines are copied. Since I do not use the term LastRow, what should I do? Sub Queries() ' ' Queries Macro ' Macro recorded 23/08/2006 by CBRE ' ' Keyboard Shortcut: Ctrl+Shift+Q ' For m = 1 To 250 With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.chintai.net/chintai/shuto/CGI/area_list.cgi?area=shuto_tokyo&city=1190_1200_1210 _1220_1230&s=0000000011111100000001000000003200000 000000000000000000010000000000220&p=" & m _ , Destination:=Range("A" & m + (271 * (m - 1)))) .Name = _ "area_list.cgi?area=shuto_tokyo&city=1190_1200_121 0_1220_1230&s=000000001111110000000100000000320000 0000000000000000000010000000000220&p=" & m .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertEntireRows .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "12" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Next m End Sub Regards Pelham Don Guillett wrote: As always, post your code for comments. You might want if lastrow64000 then destination sheet="sheet3" -- Don Guillett SalesAid Software "Pelham" wrote in message ups.com... Thanks to all of you who have assisted me learn more about VBA through this News Group - it is working for me slowly ...! In short, I have created a 20 or so line long VBA script which works fine for me sometimes. It gets the latest residential real estate data for Japan's 5 major cities and copies the relevant information straight into an Excel WorkSheet for me. The code starts with "Sub Queries()" and ends with "End Sub", but it generates some loops within the code depending upon how many different web pages it needs to visit each time. The number of web pages depends upon the number of available properties in each city whenever I choose to Run the VBA Macro. However, sometimes there are so many properties that I reach the last line of the destination WorkSheet and it stops short of what I want...! Can one of you kind folk please tell me what I have write into the script to make it automatically open a new destination WorkSheet and continue copying the data when the current WorkSheet reaches 64,000 lines, please. Also, can you tell where in the code I need to put this VBA command, please? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Queries | Excel Worksheet Functions | |||
Web Queries | Excel Programming | |||
Web Queries | Excel Discussion (Misc queries) | |||
Web queries | Excel Programming | |||
SQL Queries in VBA | Excel Programming |