I checked back the last 4 pages of threads and didn't see anything that addressed this issue, so if this has already been brought up on these forums, sorry.
I am using Excel 2003 (11.8169.8172) SP3 and I want to create a web query that will update once every ten minutes.
Among several other functions, I have this one setting up the query:
Sub VACD_Update()
'Updates the VACD tab and continues to query every 10 minutes
Dim reportlink As String
reportlink = Sheet2.Range("A6").Text
Sheet1.Activate
Sheet1.Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
With Sheet1.QueryTables.Add(Connection:=reportlink, Destination:=Range("'VACD'!A1"))
.Name = "VACD Interval report"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 10
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """tab1"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Obviously this was written by the macro recorder and only slightly modified by me to change the link (depending on the day). Other functions use the date, so I found it best to just save it to a field on the spreadsheet and call it as necessary.
Everything is actually working "fine", and the query DOES try and update every 10 minutes like it's supposed to. The problem is that about 20% of the time when it attempts to pull, it only returns the field headers. The web service the query links to is rather slow and I believe (although i have no proof and my be wrong) that I just need to force excel to take more time on the query.
Since it IS returning data, it doesn't ever give an error message, and going to the tab and forcing a manual update always works fine to fix it. The biggest problem is that while I understand how to do a manual refresh, the reason we went to the trouble of the query is so that some of our less than efficient computer users can access the same data.
Any help you guys can provide is appreciated, thank you in advance.
Reply With Quote