Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
I have a excel macro that continually gets data from the web. It will run
fine for a while and then the entire network connection from the PC hangs. I can't access any network drives or the web. If I go into DOS and type "netstat" all the connects are in "Close_Wait" state. As soon as I close Excel the network works again. The code I am using to get data from the web is : URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With If anyone has any idea what could be happenign and how to fix this please, please let me know. thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
Hi macroplay,
Are you repeatedly running the entire section of code you've shown? If so you're creating lots and lost of query tables, which may be your problem. You can create the query table once, give it a refresh interval and it will automatically update itself without any problems. Here's an example based on your code: URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .Refresh False .RefreshPeriod = 60 End With Just run this one time and it will create a query table that automatically refreshes itself every 60 minutes (you can change the RefreshPeriod property value to get a different refresh interval). -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... I have a excel macro that continually gets data from the web. It will run fine for a while and then the entire network connection from the PC hangs. I can't access any network drives or the web. If I go into DOS and type "netstat" all the connects are in "Close_Wait" state. As soon as I close Excel the network works again. The code I am using to get data from the web is : URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With If anyone has any idea what could be happenign and how to fix this please, please let me know. thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
Yes, I am running that code every 5 minutes.
Using the refresh feature would be problematic though because I have to access a few different web sites and the URLs change every day because they include a date string (I only posted one example of the web acces code). Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : loop set URL (which changes all the time) get data into 1 and only temporary worksheet (temporary worksheet data is cleared first) parse data and copy into a permanent worksheet end loop "Rob Bovey" wrote: Hi macroplay, Are you repeatedly running the entire section of code you've shown? If so you're creating lots and lost of query tables, which may be your problem. You can create the query table once, give it a refresh interval and it will automatically update itself without any problems. Here's an example based on your code: URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .Refresh False .RefreshPeriod = 60 End With Just run this one time and it will create a query table that automatically refreshes itself every 60 minutes (you can change the RefreshPeriod property value to get a different refresh interval). -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... I have a excel macro that continually gets data from the web. It will run fine for a while and then the entire network connection from the PC hangs. I can't access any network drives or the web. If I go into DOS and type "netstat" all the connects are in "Close_Wait" state. As soon as I close Excel the network works again. The code I am using to get data from the web is : URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With If anyone has any idea what could be happenign and how to fix this please, please let me know. thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
Is there a way to bring web data into a worksheet without the refresh
feature like I am attempting to do now : A query table will accept a new connection string at any time. Assuming you can automatically load your URL string variable with the correct URL required for each refresh, you'd create the initial query table as before but without setting the RefreshPeriod property, then use the following code in a loop to update the query table with the new data from each new URL: URL = "http://www.NextURL...." With Sheet1.QueryTables(1) .Connection = "URL;" & URL .Refresh False End With There's no need to clear the previous data, the query table will replace its previous data with the data from the new URL, even if the height and width of the ranges are different. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Yes, I am running that code every 5 minutes. Using the refresh feature would be problematic though because I have to access a few different web sites and the URLs change every day because they include a date string (I only posted one example of the web acces code). Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : loop set URL (which changes all the time) get data into 1 and only temporary worksheet (temporary worksheet data is cleared first) parse data and copy into a permanent worksheet end loop "Rob Bovey" wrote: Hi macroplay, Are you repeatedly running the entire section of code you've shown? If so you're creating lots and lost of query tables, which may be your problem. You can create the query table once, give it a refresh interval and it will automatically update itself without any problems. Here's an example based on your code: URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .Refresh False .RefreshPeriod = 60 End With Just run this one time and it will create a query table that automatically refreshes itself every 60 minutes (you can change the RefreshPeriod property value to get a different refresh interval). -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... I have a excel macro that continually gets data from the web. It will run fine for a while and then the entire network connection from the PC hangs. I can't access any network drives or the web. If I go into DOS and type "netstat" all the connects are in "Close_Wait" state. As soon as I close Excel the network works again. The code I am using to get data from the web is : URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With If anyone has any idea what could be happenign and how to fix this please, please let me know. thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
Sorry to ask another question...
In your example code With Sheet1.QueryTables(1) If I have done multiple QueryTables.Add on the same sheet how are they numbered. Is the first qyuery 1 or 0 and do they just increment ? thanks "Rob Bovey" wrote: Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : A query table will accept a new connection string at any time. Assuming you can automatically load your URL string variable with the correct URL required for each refresh, you'd create the initial query table as before but without setting the RefreshPeriod property, then use the following code in a loop to update the query table with the new data from each new URL: URL = "http://www.NextURL...." With Sheet1.QueryTables(1) .Connection = "URL;" & URL .Refresh False End With There's no need to clear the previous data, the query table will replace its previous data with the data from the new URL, even if the height and width of the ranges are different. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Yes, I am running that code every 5 minutes. Using the refresh feature would be problematic though because I have to access a few different web sites and the URLs change every day because they include a date string (I only posted one example of the web acces code). Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : loop set URL (which changes all the time) get data into 1 and only temporary worksheet (temporary worksheet data is cleared first) parse data and copy into a permanent worksheet end loop "Rob Bovey" wrote: Hi macroplay, Are you repeatedly running the entire section of code you've shown? If so you're creating lots and lost of query tables, which may be your problem. You can create the query table once, give it a refresh interval and it will automatically update itself without any problems. Here's an example based on your code: URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .Refresh False .RefreshPeriod = 60 End With Just run this one time and it will create a query table that automatically refreshes itself every 60 minutes (you can change the RefreshPeriod property value to get a different refresh interval). -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... I have a excel macro that continually gets data from the web. It will run fine for a while and then the entire network connection from the PC hangs. I can't access any network drives or the web. If I go into DOS and type "netstat" all the connects are in "Close_Wait" state. As soon as I close Excel the network works again. The code I am using to get data from the web is : URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True End With If anyone has any idea what could be happenign and how to fix this please, please let me know. thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
"macroplay" wrote in message
... In your example code With Sheet1.QueryTables(1) If I have done multiple QueryTables.Add on the same sheet how are they numbered. Is the first qyuery 1 or 0 and do they just increment ? They're numbered in the order in which they were created beginning with one. But in this situation you're probably safer giving them each explicit names so you're sure you know which one you're operating on. Add the following line of code to the procedure that creates the query table: .Name = "Some_Unique_Name" Then you can reference each query table by name, like so: With Sheet1.QueryTables("Some_Unique_Name") End With -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Sorry to ask another question... In your example code With Sheet1.QueryTables(1) If I have done multiple QueryTables.Add on the same sheet how are they numbered. Is the first qyuery 1 or 0 and do they just increment ? thanks "Rob Bovey" wrote: Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : A query table will accept a new connection string at any time. Assuming you can automatically load your URL string variable with the correct URL required for each refresh, you'd create the initial query table as before but without setting the RefreshPeriod property, then use the following code in a loop to update the query table with the new data from each new URL: URL = "http://www.NextURL...." With Sheet1.QueryTables(1) .Connection = "URL;" & URL .Refresh False End With There's no need to clear the previous data, the query table will replace its previous data with the data from the new URL, even if the height and width of the ranges are different. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Yes, I am running that code every 5 minutes. Using the refresh feature would be problematic though because I have to access a few different web sites and the URLs change every day because they include a date string (I only posted one example of the web acces code). Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : loop set URL (which changes all the time) get data into 1 and only temporary worksheet (temporary worksheet data is cleared first) parse data and copy into a permanent worksheet end loop "Rob Bovey" wrote: Hi macroplay, Are you repeatedly running the entire section of code you've shown? If so you're creating lots and lost of query tables, which may be your problem. You can create the query table once, give it a refresh interval and it will automatically update itself without any problems. Here's an example based on your code: URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .Refresh False .RefreshPeriod = 60 End With Just run this one time and it will create a query table that automatically refreshes itself every 60 minutes (you can change the RefreshPeriod property value to get a different refresh interval). -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
I must have something wrong. Using the following test code, the first
query.add actually gets the web data. The second query("Fx") gives a debug error "subscript out of range". If I replace the "Fx" with 1 then no data is retrived from the web. The ".refresh false" gives a "object required" debug error. ============ code Dim URL As String URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _ Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .Name = "Fx" End With MsgBox "continue" Cells.Select Selection.ClearContents Range("a1").Select URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") _ .Connection = "URL;" & URL ' refresh false End With =============================== "Rob Bovey" wrote: "macroplay" wrote in message ... In your example code With Sheet1.QueryTables(1) If I have done multiple QueryTables.Add on the same sheet how are they numbered. Is the first qyuery 1 or 0 and do they just increment ? They're numbered in the order in which they were created beginning with one. But in this situation you're probably safer giving them each explicit names so you're sure you know which one you're operating on. Add the following line of code to the procedure that creates the query table: .Name = "Some_Unique_Name" Then you can reference each query table by name, like so: With Sheet1.QueryTables("Some_Unique_Name") End With -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Sorry to ask another question... In your example code With Sheet1.QueryTables(1) If I have done multiple QueryTables.Add on the same sheet how are they numbered. Is the first qyuery 1 or 0 and do they just increment ? thanks "Rob Bovey" wrote: Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : A query table will accept a new connection string at any time. Assuming you can automatically load your URL string variable with the correct URL required for each refresh, you'd create the initial query table as before but without setting the RefreshPeriod property, then use the following code in a loop to update the query table with the new data from each new URL: URL = "http://www.NextURL...." With Sheet1.QueryTables(1) .Connection = "URL;" & URL .Refresh False End With There's no need to clear the previous data, the query table will replace its previous data with the data from the new URL, even if the height and width of the ranges are different. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Yes, I am running that code every 5 minutes. Using the refresh feature would be problematic though because I have to access a few different web sites and the URLs change every day because they include a date string (I only posted one example of the web acces code). Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : loop set URL (which changes all the time) get data into 1 and only temporary worksheet (temporary worksheet data is cleared first) parse data and copy into a permanent worksheet end loop "Rob Bovey" wrote: Hi macroplay, Are you repeatedly running the entire section of code you've shown? If so you're creating lots and lost of query tables, which may be your problem. You can create the query table once, give it a refresh interval and it will automatically update itself without any problems. Here's an example based on your code: URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .Refresh False .RefreshPeriod = 60 End With Just run this one time and it will create a query table that automatically refreshes itself every 60 minutes (you can change the RefreshPeriod property value to get a different refresh interval). -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
I also tried the following code but the 2nd query returned data from the old
URL. ============= code Dim URL As String URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _ Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .Name = "Fx" End With MsgBox "continue" Cells.Select Selection.ClearContents Range("a1").Select URL = "http://mis.nyiso.com/public/csv/rtlbmp/" & sdate & "rtlbmp_zone.csv" With ActiveWorkbook.Worksheets("Sheet1").QueryTables(1) _ .Connection = "URL;" & URL End With ActiveWorkbook.Worksheets("Sheet1").QueryTables(1) .Refresh =========== end code "macroplay" wrote: I must have something wrong. Using the following test code, the first query.add actually gets the web data. The second query("Fx") gives a debug error "subscript out of range". If I replace the "Fx" with 1 then no data is retrived from the web. The ".refresh false" gives a "object required" debug error. ============ code Dim URL As String URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _ Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .Name = "Fx" End With MsgBox "continue" Cells.Select Selection.ClearContents Range("a1").Select URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") _ .Connection = "URL;" & URL ' refresh false End With =============================== "Rob Bovey" wrote: "macroplay" wrote in message ... In your example code With Sheet1.QueryTables(1) If I have done multiple QueryTables.Add on the same sheet how are they numbered. Is the first qyuery 1 or 0 and do they just increment ? They're numbered in the order in which they were created beginning with one. But in this situation you're probably safer giving them each explicit names so you're sure you know which one you're operating on. Add the following line of code to the procedure that creates the query table: .Name = "Some_Unique_Name" Then you can reference each query table by name, like so: With Sheet1.QueryTables("Some_Unique_Name") End With -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Sorry to ask another question... In your example code With Sheet1.QueryTables(1) If I have done multiple QueryTables.Add on the same sheet how are they numbered. Is the first qyuery 1 or 0 and do they just increment ? thanks "Rob Bovey" wrote: Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : A query table will accept a new connection string at any time. Assuming you can automatically load your URL string variable with the correct URL required for each refresh, you'd create the initial query table as before but without setting the RefreshPeriod property, then use the following code in a loop to update the query table with the new data from each new URL: URL = "http://www.NextURL...." With Sheet1.QueryTables(1) .Connection = "URL;" & URL .Refresh False End With There's no need to clear the previous data, the query table will replace its previous data with the data from the new URL, even if the height and width of the ranges are different. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Yes, I am running that code every 5 minutes. Using the refresh feature would be problematic though because I have to access a few different web sites and the URLs change every day because they include a date string (I only posted one example of the web acces code). Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : loop set URL (which changes all the time) get data into 1 and only temporary worksheet (temporary worksheet data is cleared first) parse data and copy into a permanent worksheet end loop "Rob Bovey" wrote: Hi macroplay, Are you repeatedly running the entire section of code you've shown? If so you're creating lots and lost of query tables, which may be your problem. You can create the query table once, give it a refresh interval and it will automatically update itself without any problems. Here's an example based on your code: URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .Refresh False .RefreshPeriod = 60 End With Just run this one time and it will create a query table that automatically refreshes itself every 60 minutes (you can change the RefreshPeriod property value to get a different refresh interval). -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
"macroplay" wrote in message
... I must have something wrong. Using the following test code, the first query.add actually gets the web data. The second query("Fx") gives a debug error "subscript out of range". If I replace the "Fx" with 1 then no data is retrived from the web. The ".refresh false" gives a "object required" debug error. The subscript out of range error is probably the result of having created multiple query tables. Choose Insert/Name/Define with Sheet1 active and you'll probably see names like Fx_1, Fx_2, etc. Try copying the code into a brand new workbook and try again. If you have to create the same query table more than once you first need to delete its associated range name, delete the query table itself from VBA with QueryTables(x).Delete, then save the workbook, close it and re-open it. Otherwise Excel won't let you use the same unique name again. Unfortunately, it doesn't throw an error when you try, it just creates a different name by appending an underscore and a number to the name you're trying to use. This makes it a a very tricky bug. The second problem is caused by syntax errors in your With statement. There should be no line continuation character on the first line and the Refresh method needs a dot operator in front of it. The corrected code looks like this: With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") .Connection = "URL;" & URL .Refresh False End With In a brand new workbook with the above fix your code runs fine for me. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... I must have something wrong. Using the following test code, the first query.add actually gets the web data. The second query("Fx") gives a debug error "subscript out of range". If I replace the "Fx" with 1 then no data is retrived from the web. The ".refresh false" gives a "object required" debug error. ============ code Dim URL As String URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _ Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .Name = "Fx" End With MsgBox "continue" Cells.Select Selection.ClearContents Range("a1").Select URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") _ .Connection = "URL;" & URL ' refresh false End With =============================== |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
"macroplay" wrote in message
... I also tried the following code but the 2nd query returned data from the old Paste the following code into a module in a new workbook and you should see it return two data sets corresponding to the two different URLs. Sub WebQueryTest() Dim URL As String URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _ Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .Name = "Fx" End With MsgBox "Data set one" URL = "http://www.federalreserve.gov/releases/h10/summary/indexb_m.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") .Connection = "URL;" & URL .Refresh False End With MsgBox "Data set two" End Sub Keep in mind that this is just for demo purposes. The code that modifies the connection and refreshes the query table would normally need to be in a separate procedure from the one that initially creates the query table. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm URL. ============= code Dim URL As String URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _ Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .Name = "Fx" End With MsgBox "continue" Cells.Select Selection.ClearContents Range("a1").Select URL = "http://mis.nyiso.com/public/csv/rtlbmp/" & sdate & "rtlbmp_zone.csv" With ActiveWorkbook.Worksheets("Sheet1").QueryTables(1) _ .Connection = "URL;" & URL End With ActiveWorkbook.Worksheets("Sheet1").QueryTables(1) .Refresh =========== end code "macroplay" wrote: I must have something wrong. Using the following test code, the first query.add actually gets the web data. The second query("Fx") gives a debug error "subscript out of range". If I replace the "Fx" with 1 then no data is retrived from the web. The ".refresh false" gives a "object required" debug error. ============ code Dim URL As String URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _ Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .Name = "Fx" End With MsgBox "continue" Cells.Select Selection.ClearContents Range("a1").Select URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") _ .Connection = "URL;" & URL ' refresh false End With =============================== "Rob Bovey" wrote: "macroplay" wrote in message ... In your example code With Sheet1.QueryTables(1) If I have done multiple QueryTables.Add on the same sheet how are they numbered. Is the first qyuery 1 or 0 and do they just increment ? They're numbered in the order in which they were created beginning with one. But in this situation you're probably safer giving them each explicit names so you're sure you know which one you're operating on. Add the following line of code to the procedure that creates the query table: .Name = "Some_Unique_Name" Then you can reference each query table by name, like so: With Sheet1.QueryTables("Some_Unique_Name") End With -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Sorry to ask another question... In your example code With Sheet1.QueryTables(1) If I have done multiple QueryTables.Add on the same sheet how are they numbered. Is the first qyuery 1 or 0 and do they just increment ? thanks "Rob Bovey" wrote: Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : A query table will accept a new connection string at any time. Assuming you can automatically load your URL string variable with the correct URL required for each refresh, you'd create the initial query table as before but without setting the RefreshPeriod property, then use the following code in a loop to update the query table with the new data from each new URL: URL = "http://www.NextURL...." With Sheet1.QueryTables(1) .Connection = "URL;" & URL .Refresh False End With There's no need to clear the previous data, the query table will replace its previous data with the data from the new URL, even if the height and width of the ranges are different. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... Yes, I am running that code every 5 minutes. Using the refresh feature would be problematic though because I have to access a few different web sites and the URLs change every day because they include a date string (I only posted one example of the web acces code). Is there a way to bring web data into a worksheet without the refresh feature like I am attempting to do now : loop set URL (which changes all the time) get data into 1 and only temporary worksheet (temporary worksheet data is cleared first) parse data and copy into a permanent worksheet end loop "Rob Bovey" wrote: Hi macroplay, Are you repeatedly running the entire section of code you've shown? If so you're creating lots and lost of query tables, which may be your problem. You can create the query table once, give it a refresh interval and it will automatically update itself without any problems. Here's an example based on your code: URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Fx").QueryTables.Add( _ Connection:="URL;" & URL, Destination:=Worksheets("Fx").Cells(1, 1)) .BackgroundQuery = True .Refresh False .RefreshPeriod = 60 End With Just run this one time and it will create a query table that automatically refreshes itself every 60 minutes (you can change the RefreshPeriod property value to get a different refresh interval). -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
Thanks for your help Rob. I'm going to leave my macro running overnihgt to
see if this helped. john "Rob Bovey" wrote: "macroplay" wrote in message ... I must have something wrong. Using the following test code, the first query.add actually gets the web data. The second query("Fx") gives a debug error "subscript out of range". If I replace the "Fx" with 1 then no data is retrived from the web. The ".refresh false" gives a "object required" debug error. The subscript out of range error is probably the result of having created multiple query tables. Choose Insert/Name/Define with Sheet1 active and you'll probably see names like Fx_1, Fx_2, etc. Try copying the code into a brand new workbook and try again. If you have to create the same query table more than once you first need to delete its associated range name, delete the query table itself from VBA with QueryTables(x).Delete, then save the workbook, close it and re-open it. Otherwise Excel won't let you use the same unique name again. Unfortunately, it doesn't throw an error when you try, it just creates a different name by appending an underscore and a number to the name you're trying to use. This makes it a a very tricky bug. The second problem is caused by syntax errors in your With statement. There should be no line continuation character on the first line and the Refresh method needs a dot operator in front of it. The corrected code looks like this: With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") .Connection = "URL;" & URL .Refresh False End With In a brand new workbook with the above fix your code runs fine for me. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... I must have something wrong. Using the following test code, the first query.add actually gets the web data. The second query("Fx") gives a debug error "subscript out of range". If I replace the "Fx" with 1 then no data is retrived from the web. The ".refresh false" gives a "object required" debug error. ============ code Dim URL As String URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _ Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .Name = "Fx" End With MsgBox "continue" Cells.Select Selection.ClearContents Range("a1").Select URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") _ .Connection = "URL;" & URL ' refresh false End With =============================== |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
Rob, I hope you're still listening...
The fix didn't help. the Macro ran for about 13 hours then the network interface hung. To speed up coding the fix all I did was delete all the defined names and querytables at the end of every loop of the macro instead of reusing querytables. I assumed this would have the same effect. Would you have any other ideas to try ? Also let me know if email is preferred. thanks, john "macroplay" wrote: Thanks for your help Rob. I'm going to leave my macro running overnihgt to see if this helped. john "Rob Bovey" wrote: "macroplay" wrote in message ... I must have something wrong. Using the following test code, the first query.add actually gets the web data. The second query("Fx") gives a debug error "subscript out of range". If I replace the "Fx" with 1 then no data is retrived from the web. The ".refresh false" gives a "object required" debug error. The subscript out of range error is probably the result of having created multiple query tables. Choose Insert/Name/Define with Sheet1 active and you'll probably see names like Fx_1, Fx_2, etc. Try copying the code into a brand new workbook and try again. If you have to create the same query table more than once you first need to delete its associated range name, delete the query table itself from VBA with QueryTables(x).Delete, then save the workbook, close it and re-open it. Otherwise Excel won't let you use the same unique name again. Unfortunately, it doesn't throw an error when you try, it just creates a different name by appending an underscore and a number to the name you're trying to use. This makes it a a very tricky bug. The second problem is caused by syntax errors in your With statement. There should be no line continuation character on the first line and the Refresh method needs a dot operator in front of it. The corrected code looks like this: With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") .Connection = "URL;" & URL .Refresh False End With In a brand new workbook with the above fix your code runs fine for me. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "macroplay" wrote in message ... I must have something wrong. Using the following test code, the first query.add actually gets the web data. The second query("Fx") gives a debug error "subscript out of range". If I replace the "Fx" with 1 then no data is retrived from the web. The ".refresh false" gives a "object required" debug error. ============ code Dim URL As String URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _ Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1, 1)) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .Name = "Fx" End With MsgBox "continue" Cells.Select Selection.ClearContents Range("a1").Select URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt" With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") _ .Connection = "URL;" & URL ' refresh false End With =============================== |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
"macroplay" wrote in message
... The fix didn't help. the Macro ran for about 13 hours then the network interface hung. To speed up coding the fix all I did was delete all the defined names and querytables at the end of every loop of the macro instead of reusing querytables. I assumed this would have the same effect. 13 hours? How many of these are you running? It's quite possible there's some kind of minor resource leak going on that wouldn't normally cause problems until you run query tables for very long periods of time. My best advice would be to break this up into smaller batches and restart Excel in between. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
The macro runs every 5 minutes from an excel timer trigger. Every 5minute run
the macro does about 40 or more qureytables out to various web pages. At the end of the run it starts the 5 minute excel timer again. So basically the macro runs endlessly (or I want it to) every 5 minutes. I guess I'll have to consider your advice. Thanks, john "Rob Bovey" wrote: "macroplay" wrote in message ... The fix didn't help. the Macro ran for about 13 hours then the network interface hung. To speed up coding the fix all I did was delete all the defined names and querytables at the end of every loop of the macro instead of reusing querytables. I assumed this would have the same effect. 13 hours? How many of these are you running? It's quite possible there's some kind of minor resource leak going on that wouldn't normally cause problems until you run query tables for very long periods of time. My best advice would be to break this up into smaller batches and restart Excel in between. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
At a guess, 40 query tables every 5 minutes = 7.5 seconds/QT.
If a site is slow to answer and/or depending on the production of your final web pages, the timer could trying to fire again before all your refreshes are done, if you have not disabled the before. Also, you do know that MS does not recommend/support Excel (or Office application) running in such an environment, as they were/are not designed for continuous use. Rob's suggestion of Start/Close Excel at some regular interval may clear it. But your have these kind of requirements, Excel may not be the best tool. NickHK "macroplay" wrote in message ... The macro runs every 5 minutes from an excel timer trigger. Every 5minute run the macro does about 40 or more qureytables out to various web pages. At the end of the run it starts the 5 minute excel timer again. So basically the macro runs endlessly (or I want it to) every 5 minutes. I guess I'll have to consider your advice. Thanks, john "Rob Bovey" wrote: "macroplay" wrote in message ... The fix didn't help. the Macro ran for about 13 hours then the network interface hung. To speed up coding the fix all I did was delete all the defined names and querytables at the end of every loop of the macro instead of reusing querytables. I assumed this would have the same effect. 13 hours? How many of these are you running? It's quite possible there's some kind of minor resource leak going on that wouldn't normally cause problems until you run query tables for very long periods of time. My best advice would be to break this up into smaller batches and restart Excel in between. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Network Hung
Yes, I'm redesigning the code to start-stop.
As a side note I had some of the excel sheets publishing to the IIS server on my local C drive and republishing every 5minutes when the files got saved. When I removed these the system has been running for 24 hours. I think there's still some memory leaks so I'll continue with Rob's suggestion. john "NickHK" wrote: At a guess, 40 query tables every 5 minutes = 7.5 seconds/QT. If a site is slow to answer and/or depending on the production of your final web pages, the timer could trying to fire again before all your refreshes are done, if you have not disabled the before. Also, you do know that MS does not recommend/support Excel (or Office application) running in such an environment, as they were/are not designed for continuous use. Rob's suggestion of Start/Close Excel at some regular interval may clear it. But your have these kind of requirements, Excel may not be the best tool. NickHK "macroplay" wrote in message ... The macro runs every 5 minutes from an excel timer trigger. Every 5minute run the macro does about 40 or more qureytables out to various web pages. At the end of the run it starts the 5 minute excel timer again. So basically the macro runs endlessly (or I want it to) every 5 minutes. I guess I'll have to consider your advice. Thanks, john "Rob Bovey" wrote: "macroplay" wrote in message ... The fix didn't help. the Macro ran for about 13 hours then the network interface hung. To speed up coding the fix all I did was delete all the defined names and querytables at the end of every loop of the macro instead of reusing querytables. I assumed this would have the same effect. 13 hours? How many of these are you running? It's quite possible there's some kind of minor resource leak going on that wouldn't normally cause problems until you run query tables for very long periods of time. My best advice would be to break this up into smaller batches and restart Excel in between. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do you set up a network. | Setting up and Configuration of Excel | |||
Using an Add In on a Network | Excel Discussion (Misc queries) | |||
Help - where to put VBCode on a network | Excel Programming | |||
Any automation calls gets hung when the Excel workbook has an active cell. | Excel Programming | |||
Copying files ...Network to Network | Excel Programming |