Kim, Since no one with more smarts than I have about this has jumped in, I'll
offer up something that you can perhaps use or alter to suit your needs.
This is from a setup used to update some team stats via a web query. I truly
don't understand all I know about it, but it works.
The setup: initially I created the web query and saved it (the .iqy file) in
the same folder with the workbook that uses it. You'll see in the code where
when things go wrong, it looks in the same folder to try to find it. The
name of the .iqy file as I named it is used, you'll need to change that.
Code goes to the page used to retrieve the data, executes the query and
failing that, sets up to use the query. The name associated with the query
area on the sheet is removed to keep from creating zillions of entries in the
workbook's Names collection.
After the data is retrieved, the code goes on (not posted here) to grab what
it needs from the worksheet and use it elsewhere. If you think I can help
more, feel free to contact me at HelpFrom at jlathamsite.com - but I don't
use Web Queries much at all, and know very little more than what is shown
here in the code. The variables used in the code, such as
ImportedStatsSheet, PathToQuery (both are type String) and anyWebQuery (type
Object) were defined earlier - ahead of this code.
Sheets(ImportedStatsSheet).Select
Cells.Select
Selection.ClearContents
Range("A1").Select
On Error Resume Next
Selection.QueryTable.Refresh BackgroundQuery:=False
If Err <> 0 Then
'must rebuild query and then requery
Err.Clear
On Error GoTo 0
PathToQuery = ThisWorkbook.FullName
For LC = Len(PathToQuery) To 1 Step -1
If Mid$(PathToQuery, LC, 1) = "\" Then
PathToQuery = Left(PathToQuery, LC) & "StarFireStats.iqy"
Exit For
End If
Next
'define this as to the path to file StarFireStats.iqy
QueryConnectString = "FINDER;" & PathToQuery
For Each anyWebQuery In ActiveSheet.QueryTables
anyWebQuery.Delete
Next
With ActiveSheet.QueryTables.Add(Connection:=QueryConnectString,
Destination:=Range("A1"))
.Name = "StarFireStats"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End If
On Error GoTo 0
'we should have data from the website now
'redefine the lookup table based on imported data
