GetValue work with UNC?

[theta]

Hi all...

I have a working macro, but now it fails when I try to use a UNC path...

[vba]
Sub TestGetValue()
' p = "\\Lonfile01\Operations\SDR MASTER"
p = "C:\IMPORT\DATA"
' f = "sdr_rates.xlsx"
f = "import_file.xlsx"
s = "Sheet1"
a = "A3"
MsgBox GetValue(p, f, s, a)
End Sub

Private 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(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
[/vba]

Could I use Ron De Bruin's ChDirNet method...how would I implement this?

Any help appreciated...

[vba]
Option Explicit
'#If VBA7 Then
' Declare PtrSafe Function SetCurrentDirectoryA Lib _
' "kernel32" (ByVal lpPathName As String) As Long
'#Else
Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
'#End If
Sub ChDirNet(szPath As String)
SetCurrentDirectoryA szPath
End Sub
' Note: If you want to use one of the first four macros in your own workbook do not forget
' to copy the macro GetData from the FunctionModule in your workbook
[/vba]

[theta]

This is what I have been trying but it fails on the ChDirNet call (Function expected) and won't work past it

[vba]

'Option Explicit
'#If VBA7 Then
' Declare PtrSafe Function SetCurrentDirectoryA Lib _
' "kernel32" (ByVal lpPathName As String) As Long
'#Else
Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
'#End If

Sub ChDirNet(szPath As String)
SetCurrentDirectoryA szPath
End Sub
' Note: If you want to use one of the first four macros in your own workbook do not forget
' to copy the macro GetData from the FunctionModule in your workbook

Sub TestGetValue()
p = "\\Lonfile01\Operations\SDR MASTER\"
' p = "C:\IMPORT\DATA"
f = "sdr_rates.xlsx"
' f = "import_file.xlsx"
s = "Sheet1"
a = "A3"
MsgBox GetValue(p, f, s, a)
End Sub

Private 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
If ChDirNet(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
[/vba]

[Bob Phillips]

ChDirNet is a Sub not a Function.

[theta]

So how should I work it?

[theta]

I have broken it down to it's simplest form and it still does not work :

[vba]

Sub TesdtGetValueTest()

MsgBox GetValueTest

End Sub

Function GetValueTest()

ref = "A3"
arg = "'" & "\\Lonfile01\Operations\SDR MASTER\[sdr_table.xlsx]" & "Sheet1" & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
' arg = "'" & "C:\IMPORT\DATA\[import_file.xlsx]" & "Sheet1" & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
GetValueTest = ExecuteExcel4Macro(arg)

End Function
[/vba]

Running the sub produces the correct result in the MsgBox, but just calling the function does not work =GetValueTest - I get a #VALUE error

[Kenneth Hobs]

Run it as a routine in a Sub, not as a UDF.

Otherwise, try an alternate route. http://www.tech-archive.net/Archive/.../msg03288.html