Debug problem with Zorveks excel macro getcellfromclosedworkbook

In 2007 Zorvek provided a macro here to obtain a cell from a closed workbook (replacing the indirect function which fails on a closed workbook).  Zorvek's macro is shown in the code section of this question

I am trying to use this and not sure how to start finding my problem

I have the following in a cell:

=getcellfromclosedworkbook("C:\","test1.xlsx","sheet1","A1")

I tried changing the path and file name to bad paths and file names and got the error from the macro #Bad Path or File! (so I know it is executing.

Beyond that - all I get is #value! as a result.  FYI - The cell being referenced has 9999.99 in it.

What might I be doing wrong?

Running Excel 2007 under XP

Public Function GetCellFromClosedWorkbook( _
      ByVal WorkbookPath As String, _
      ByVal WorkbookName As String, _
      ByVal WorksheetName As String, _
      ByVal CellReference As String, _
      Optional ByVal FirstRow As Long, _
      Optional ByVal FirstColumn As Long _
   ) As Variant

' Read a cell value from a closed workbook. WorkbookPath is a string containing the full path to the folder
' containing the closed workbook. WorkbookName is a string containing the workbook name including the file
' extension. WorksheetName is a string containing the name of the worksheet from which to pull the value.
' CellReference is a string containing the cell address in A1 reference style. The closed workbook cannot
' be password protected.

   Dim Connection As Object
   Dim RecordSet As Object
   Dim SQL As String
   
   ' Append trailing slash
   If Right(WorkbookPath, 1) <> "\" Then
      WorkbookPath = WorkbookPath & "\"
   End If
   
   ' Check for valid path name
   If Len(Dir(WorkbookPath & WorkbookName)) = 0 Then
      GetCellFromClosedWorkbook = "#Bad Path or File!"
      Exit Function
   End If

   ' Open the ADODB connection and record set
   Set Connection = CreateObject("ADODB.Connection")
   With Connection
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Extended Properties") = "Excel 8.0;HDR=NO;IMEX=1"
      .Open WorkbookPath & WorkbookName
   End With
   Set RecordSet = CreateObject("ADODB.Recordset")
   RecordSet.Open "SELECT * From [" & WorksheetName & "$]", Connection, 1, 1 ' adOpenKeyset, adLockReadOnly
   
   ' Check range
   If Range(CellReference).Column - FirstColumn > RecordSet.Fields.Count _
      Or Range(CellReference).Row <= FirstRow _
      Or Range(CellReference).Column <= FirstColumn _
   Then
      GetCellFromClosedWorkbook = "#Bad Reference!"
      Exit Function
   End If

   ' Pull the cell value from the record set
   If Range(CellReference).Row - FirstRow = 1 Then
      GetCellFromClosedWorkbook = RecordSet.Fields(Range(CellReference).Column - FirstColumn - 1)
   Else
      RecordSet.Move Range(CellReference).Row - FirstRow - 2
      If RecordSet.EOF Then
         GetCellFromClosedWorkbook = "#Bad Reference!"
         Exit Function
      End If
      GetCellFromClosedWorkbook = RecordSet.Fields(Range(CellReference).Column - FirstColumn - 1)
   End If
   
   ' Clean up
   RecordSet.Close
   Connection.Close

End Function

Select all Open in new window