Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Web Query Help
Hello,
I'm having a difficult time with Web Queries. I have set up a web query that automatically updates every 10 minutes. I would like to save the data from each of the updates in a separate sheet. So far I have had no luck. Can anyone point me in the correct direction? Thanks in advance. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Web Query Help
Peter
How are you currently refreshing every 10 minutes? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "PeterJCollins" wrote in message s.com... Hello, I'm having a difficult time with Web Queries. I have set up a web query that automatically updates every 10 minutes. I would like to save the data from each of the updates in a separate sheet. So far I have had no luck. Can anyone point me in the correct direction? Thanks in advance. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Web Query Help
I'm using the automatic refresh option.
Nick Hodge wrote: Peter How are you currently refreshing every 10 minutes? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "PeterJCollins" wrote in message s.com... Hello, I'm having a difficult time with Web Queries. I have set up a web query that automatically updates every 10 minutes. I would like to save the data from each of the updates in a separate sheet. So far I have had no luck. Can anyone point me in the correct direction? Thanks in advance. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Web Query Help
Peter
You could run the code below (FireRefresh). This then runs the query every 10 minutes to a new worksheet (You will need to record yourself navigating to your web data and paste that in the querystring. It can have a latest time set but it will loop otherwise until stops (Bear in mind that this may error in versions before XL2000 SP2 (I think) as adding sheets may error as it's code name hits a limit Sub FireRefresh() Application.OnTime Now() + TimeValue("00:10:00"), "RefreshAndMove" End Sub Sub RefreshAndMove() ActiveWorkbook.Worksheets.Add With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://moneycentral.msn.com/detail/stock_quote?Symbol=msft", Destination _ :=Range("A1")) .Name = "msft" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 1 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = """pgMstr""" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Call FireRefresh End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "PeterJCollins" wrote in message ps.com... I'm using the automatic refresh option. Nick Hodge wrote: Peter How are you currently refreshing every 10 minutes? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "PeterJCollins" wrote in message s.com... Hello, I'm having a difficult time with Web Queries. I have set up a web query that automatically updates every 10 minutes. I would like to save the data from each of the updates in a separate sheet. So far I have had no luck. Can anyone point me in the correct direction? Thanks in advance. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Web Query Help
Thank you for your prompt response. I will try it out.
On Dec 27, 2:48 pm, "Nick Hodge" wrote: Peter You could run the code below (FireRefresh). This then runs the query every 10 minutes to a new worksheet (You will need to record yourself navigating to your web data and paste that in the querystring. It can have a latest time set but it will loop otherwise until stops (Bear in mind that this may error in versions before XL2000 SP2 (I think) as adding sheets may error as it's code name hits a limit Sub FireRefresh() Application.OnTime Now() + TimeValue("00:10:00"), "RefreshAndMove" End Sub Sub RefreshAndMove() ActiveWorkbook.Worksheets.Add With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://moneycentral.msn.com/detail/stock_quote?Symbol=msft", Destination _ :=Range("A1")) .Name = "msft" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 1 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = """pgMstr""" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Call FireRefresh End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England e.co.uk "PeterJCollins" wrote in glegroups.com... I'm using the automatic refresh option. Nick Hodge wrote: Peter How are you currently refreshing every 10 minutes? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk "PeterJCollins" wrote in message ups.com... Hello, I'm having a difficult time with Web Queries. I have set up a web query that automatically updates every 10 minutes. I would like to save the data from each of the updates in a separate sheet. So far I have had no luck. Can anyone point me in the correct direction? Thanks in advance.- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems importing from an Access query | Excel Discussion (Misc queries) | |||
changing query source | Excel Worksheet Functions | |||
enable automatic refresh | Excel Worksheet Functions | |||
AHHH! Again | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) |