We help IT Professionals succeed at work.
Get Started

Debug problem with Zorveks excel macro getcellfromclosedworkbook

kenwiens
kenwiens asked
on
387 Views
Last Modified: 2012-06-27
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

Open in new window

Comment
Watch Question
VBA Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE