Web Query Help

  • Thread starter Thread starter PeterJCollins
  • Start date Start date
P

PeterJCollins

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.
 
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-mail address removed)
www.nickhodge.co.uk
 
Back
Top