PDA

View Full Version : GetValue work with UNC?



theta
02-07-2012, 08:38 AM
Hi all...

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


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


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

Any help appreciated...


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

theta
02-07-2012, 08:52 AM
This is what I have been trying but it fails on the ChDirNet call (Function expected) and won't work past it



'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

Bob Phillips
02-07-2012, 09:00 AM
ChDirNet is a Sub not a Function.

theta
02-07-2012, 09:05 AM
So how should I work it?

theta
02-07-2012, 09:23 AM
I have broken it down to it's simplest form and it still does not work :



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


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
02-07-2012, 01:18 PM
Run it as a routine in a Sub, not as a UDF.

Otherwise, try an alternate route. http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.worksheet.functions/2006-08/msg03288.html