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.

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.

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.

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.

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 -