Get Data from a closed workbook Excel 2007

[jtrejo85]

Hi everyone, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I'm new using VB and I'm having some troubles to get data from a closed wb in Excel 2007, I found a code which I tried to use but doesn't work:<o></o>
___________________________<o></o>
Public Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
'Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(True, True, xlR1C1)<o></o>
' Execute an XLM macro
On Error Resume Next
GetValue = ExecuteExcel4Macro(arg)
End Function<o></o>
__________________________<o></o>
<o></o>
The function does makes sure that the file exists but something happens when it tries to retrieve the data from the wb, because I always get the value "0". I'm using it like this:<o></o>
<o></o>
p4: "C:\Users\CARDSA\Documents\CARD\Generator" (which I got playing a bit with the text formulas and CELL("filename"))<o></o>
q4: "file1.xlsx" (also comes from text formulas and CELL())<o></o>
r4: "Estimation" (also comes from text formulas and CELL())<o></o>
s4: "$K$59"<o></o>
<o></o>
n4: =GetValue(p4,q4,r4,s4)<o></o>
<o></o>
I always get zero!!!, I think the problem is in the argument range reference but I'm not sure, could someone help me out please?<o></o>
<o></o>
Thanks,<o></o>
JT<o></o>

 

[Domenic]

The GetValue function does not work if used in a worksheet formula. It can, however, be used with VBA. For example...

Sub test()

    Dim p As String, f As String
    Dim s As String, a As String
    
    p = Range("P4").Value
    f = Range("Q4").Value
    s = Range("R4").Value
    a = Range("S4").Value
    
    MsgBox GetValue(p, f, s, a)
    
End Sub

Of course, one can always link a formula to the closed workbook...

='C:\Users\CARDSA\Documents\CARD\Generator\[file1.xlsx]Estimation'!$K$59

 

[jtrejo85]

Domenic,

It is not working either, and I can not use a simple link (='C:.....) because I'll share this file and the paths are going to change.

Thanks anyway, any other ideas? please.

JT

 

[Domenic]

It is not working either,...

What do you mean by it's not working? Are you getting an error? If so, which one?

...and I can not use a simple link (='C:.....) because I'll share this file and the paths are going to change.

If you download and install the free add-in Morefunc, you can use INDIRECT.EXT...

=INDIRECT.EXT("'"&P4&"\["&Q4&"]"&R4&"'!"&S4)

The add-in can be downloaded here...

http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

Note that there's no need for other users to install the add-in. The function can be imbedded within the file itself.

 

[jtrejo85]

The INDIRECT.EXT worked, is awesome! thank you very much!