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>
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,000
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
 

jtrejo85

New Member
Joined
Sep 3, 2010
Messages
3
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,000
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.
 

Forum statistics

Threads
1,081,420
Messages
5,358,574
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top