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