Running Excel Web Query & Importing to Access - Revisted

[gbrendemuehl]

I am getting an error implementing the solution presented in the previous thread titled "Running Excel Web Query & Importing to Access". The error is "Error 9 - Subscript out of range" on the statement "Set objQueryTable = objWorkSheet.QueryTables(strQueryTable)"

Here is my code:

Private Sub cmdTrack_Click()
Status = UpdateExcelQueryTable("c:\Documents and Settings\Administrator\My Documents\The Orchid Grove\qryUSPS.xls", "Sheet1", "USPSTracking")

Public Function UpdateExcelQueryTable(strWorkbook As String, strSheet As String, strQueryTable As String) As Boolean

On Error GoTo Err_Handler
'
Dim appExcel As Object
Dim objWorkSheet As Object
Dim objQueryTable As Object
'
Set appExcel = GetObject(strWorkbook)
Set objWorkSheet = appExcel.Worksheets(strSheet)
objWorkSheet.Visible = True
Set objQueryTable = objWorkSheet.QueryTables(strQueryTable)
objQueryTable.Refresh False
While objQueryTable.Refreshing
  'Do Nothing
Wend
Set objQueryTable = Nothing
Set objWorkSheet = Nothing
appExcel.Close True
Set appExcel = Nothing
UpdateExcelQueryTable = True
Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "UpdateExcelQueryTable()"
    UpdateExcelQueryTable = False
    Resume Exit_Handler
End Function

I have checked the filename, sheet and query names and they look correct. I'd appreciate you help.

Thanks
Gary

 

[PHV]

Does qryUSPS.xls have a QueryTable named USPSTracking in a sheet named Sheet1 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gbrendemuehl]

That was the first thing I checked. I found the query file "USPSTracking.iqy in c:\Documents...\Application Data\Microsoft\Queries. And it is definitely on Sheet1. It's probably something simple, but I'm not seeing it.

 

[PHV]

USPSTracking.iqy Isn't a QueryTable !

 

[gbrendemuehl]

Found it. I thought that when I saved the query, the file name and the query name would be the same. I found where the name is set in the spreadsheet, changed it and now it works.

Thanks for your help.
Gary