Using VBA to open a closed excel file

[KColgrove]

I am trying to make a function that will gather 4 strings that will end up displaying a value from another excel worksheet that is closed. When I use the function I get the return #VALUE! is there something that I'm missing?

Public Function GetValueFromClosedWorkbook(path, file, sheet, ref)
Dim arg As String
' Let's check whether the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValueFromClosedWorkbook = "File Not Found"
Exit Function
End If
' We create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("I12").Address(, , xlR1C1)
'MsgBox arg
' Now we execute an XLM macro
'All references must be given as R1C1 strings.
GetValueFromClosedWorkbook = ExecuteExcel4Macro(arg)
End Function

[bakerman2]

Works fine for me like this.

Please Login or Register  to view this content.

PS: Please use Code Tags when posting code. It makes your code much easier to read and copy for testing, and it also maintains VBA formatting.