Get Data from a closed workbook Excel 2007

jtrejo85

New Member
Joined
Sep 3, 2010
Messages
3
Hi everyone, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
___________________________<o:p></o:p>
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:p></o:p>

' Execute an XLM macro
On Error Resume Next
GetValue = ExecuteExcel4Macro(arg)
End Function<o:p></o:p>

__________________________<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
p4: "C:\Users\CARDSA\Documents\CARD\Generator" (which I got playing a bit with the text formulas and CELL("filename"))<o:p></o:p>
q4: "file1.xlsx" (also comes from text formulas and CELL())<o:p></o:p>
r4: "Estimation" (also comes from text formulas and CELL())<o:p></o:p>
s4: "$K$59"<o:p></o:p>
<o:p></o:p>
n4: =GetValue(p4,q4,r4,s4)<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Thanks,<o:p></o:p>
JT<o:p></o:p>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
The GetValue function does not work if used in a worksheet formula. It can, however, be used with VBA. For example...

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top