VBA to Reference a file when not logged in? unc address?
 

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

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

VBA to Reference a file when not logged in? unc address?
 

Much thanks!

"Dave Peterson" wrote:

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