Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink to URL needs login even though I'm already logged in | Excel Discussion (Misc queries) | |||
Need to know how many users are logged in... | Excel Discussion (Misc queries) | |||
Getting Logged in User Name in formula... | Excel Discussion (Misc queries) | |||
How do you reference the logged on user name in excel? | Excel Discussion (Misc queries) | |||
Reference Row Address | Excel Programming |