OK, I added some DEBUG.PRINT code after reworking the connection statement. It now looks like this:
With Worksheets(1).QueryTables.Add(Connection:=sVanstat, Destination:=Range("Sheet1!A1"))
.Name = "vanstat"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With
Debug.Print Worksheets(1).QueryTables("vanstat").Name
Debug.Print Worksheets(1).QueryTables("vanstat").Connection
Debug.Print Worksheets(1).QueryTables("vanstat").MaintainConnection
Debug.Print Worksheets(1).QueryTables("vanstat").QueryType
Debug.Print Worksheets(1).QueryTables("vanstat").WebSelectionType
Debug.Print Worksheets(1).QueryTables("vanstat").SourceDataFile
With Worksheets(1).QueryTables("vanstat")
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Name = "HostAliasTable"
The strange thing is that it now works most of the time. But once a problem develops I must shut down Excel and restart before I can try to run the macro again. Does Excel keep a connection open if this kind of error is found during processing so that the macro cannot be rerun until Excel is closed? Any idea why it seems to be working now even though the only changes to the macro are the DEBUG.PRINT lines and moving the REFRESH statement to a separate WITH statement? Confused, but not so frustrated, in Atlanta.