Access data in a closed workbook with ExecuteExcel4Macro

[lisamariechemistry]

Hi -

I'm trying to access data from a cell in a closed workbook, so I read
previous posts about it and started with a "simplest case" test, which
works fine:


debug.print ExecuteExcel4Macro("'c:\[TEST.xls]Sheet1'!R1C1")
'yields the value of cell A1
'with TEST.xls as a new workbook created by me - nothing done to it
except to add some text in cell A1

BUT: when i try it with the file I actually want to access, I
encounter problems...

debug.print ExecuteExcel4Macro("'c:\[SURR01.xls]Sheet1'!R1C1")
'yields Error 2023

However, if c:\SURR01.xls is open, ExecuteExcel4Macro does yield the
correct results.

I've triple and quadruple checked my syntax, looking for any typos,
and Sheet1 is definitely a sheet in the workbook. So: what else
could be different about this workbook to cause the error? It isn't
protected. It is generated by specialized software (HP CHEMSTATION)
but as far as I can tell is a normal (and fairly simple) excel
workbook, nothing but text in the cells.

If need be, I can easily write the program to open all the files to
access the data, but I'm thinking somebody out there knows the answer
to my mystery. Thanks! -Lisa

 

[Kenneth Hobson]

Maybe this will help.

Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As
String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" &
Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)

End Function

 

[Rick Rothstein \(MVP - VB\)]

My guess is that SURR01.xls is in another directory from where your
documents are stored by default. Try putting the whole path to it in front
of the left square bracket, something like this...

Debug.Print ExecuteExcel4Macro("'C:\Dir1\Dir2\[SURR01.xls]Sheet1'!R1C1")

Replacing the C:\Dir1\Dir2 with whatever directory path your file is stored
at.

Rick

 

[lisamariechemistry]

Rick - that's definitely not the problem! I tried that first, but
since the directory string is long and complicated, I wanted to
elimiate that as a possible source of error and therefore copied the
file to my c directory.

 

[Rick Rothstein \(MVP - VB\)]

Unfortunately, then, I cannot duplicate your problem on my system. Moving
the file into the root of C: worked fine for me... your statement reported
the contents of the cell from the closed file with no problem. One thing I
found interesting though... **before** I move the file, I tried your
statement both with and without the "C:\" part and, in both cases, if found
the file in the default directory. That really surprised me... prefixing
"C:\" in front of the left square bracket had no effect on the file being
found. I'm guessing Excel looks in the default directory if it can't find
the file in the specified location (or is it the reverse of this... it looks
in the default location first?).

Rick

 

[lisamariechemistry]

Well, I've tested out Kenneth's function now. As I suspected, it's
just a longer way of reaching the same result that I was getting:
which is that it seems to work fine for any file anywhere in my
network except these files generated by the chemstation software,
leading me to believe there is some difference in the SURR01.xls
workbook itself, though I can't find any. Okay, well thanks for
trying. I guess It's back to opening hundreds of files for me! -Lisa

Oh by the way, there is an error in Kenneth's function.

If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function

You're adding "\" twice