VBA to Reference a file when not logged in? unc address?
But here's a post from Rob Bovey/Tom Ogilvy that works with UNC paths.
========
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Example of usage
Sub GetFile()
On Error GoTo ErrHandler
ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp"
Exit sub
ErrHandler:
MsgBox "Couldn't set path"
End Sub
But you don't have to change drive/folder to use the dir() test:
DataDir = Dir("wkbk_B.xls")
could be:
DataDir = Dir("\\myserver\myfolder\mysubfolder\wkbk_B.xls")
And just drop the ChDrive and chDir totally.
IXLINXL wrote:
Hi all. I am using the Office 2000 package and I hav created some nifty
macros (With help from some of you wizards of course) But I am having
problems makeing them work while logged off.
wkbk A needs to access wkbk B and currently I am referencig it with code
something like this:
FilePath = "X:\folder1\Customer"
'change drive to FilePath
ChDrive FilePath
'change directory to FilePath
ChDir FilePath
DataDir = Dir("wkbk_B.xls")
'If the file does not exist then go to exitline
If Len(DataDir) = 0 Then GoTo exitline
CustDir = Dir("wkbk_C.xls")
'If the file does not exist then..
If Len(CustDir) = 0 Then GoTo exitline
This works fine when I am logged in but when I set up the task scheduler to
run this I am not logged in and it fails. I am pretty sure it is failing
because the "X" drive is not mapped while I am not logged in.
I have tried using the UNC address in place of the drive association but it
does not work.
Any other suggestions how I might reference and open file wkbk_B.xls ??
IXLINXL - not
--
Dave Peterson
|