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>
<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>