ChDrive error (server path)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a macro that goes through each file in the folder containing the
workbook and combines their contents together. I used to have this folder on
my computer and it worked fine. I have now moved this folder to a server on
our network, and am getting:

"Run Time Error '5', Invalid Procedure Call or argument."

The path is "\\xxxxx-s2\documents\Marcotte\....."

I recently got a new computer, which was one of the other servers (we used
to have 3, now we have 1). Before, I had to log onto the servers with a
username and password, but since the server/computer change I have not had to
do that.

Sub OpenAll()
'this macro compiles all sbt files in the current directory into a total file
'which can be used to create an IIF.
Dim myPath As String
Dim FNames As String
Dim fs As Object
Dim i As Integer
Dim DlySBTWkb As Workbook
Dim wkb As Workbook
Dim DlyLastRow As Single
Dim MlyLastRow As Single

myPath = ThisWorkbook.Path
ChDrive myPath '.......... <--- generates error
ChDir myPath

'More Code that does the actual work

End Sub

TIA,
Marcotte
 
Thanks Ron. I'm looking at that site right now. Any idea why this code
works for a folder on my machine, but not for one on a network?

myPath = ThisWorkbook.Path
ChDrive myPath '.......... <--- generates error
ChDir myPath

"ThisWorkbook" is in the correct folder on the network. I would think that
"ThisWorkbook.Path" would give me the correct string to pass to ChDrive.
 
Hi Marcotte

Why i don't know.
But you can try ChDirNet

http://www.rondebruin.nl/copy3.htm#select
See example 6

--
Regards Ron de Bruin
http://www.rondebruin.nl


Marcotte A said:
Thanks Ron. I'm looking at that site right now. Any idea why this code
works for a folder on my machine, but not for one on a network?

myPath = ThisWorkbook.Path
ChDrive myPath '.......... <--- generates error
ChDir myPath

"ThisWorkbook" is in the correct folder on the network. I would think that
"ThisWorkbook.Path" would give me the correct string to pass to ChDrive.
Click to expand...
 
ChDrive doesn't work with a URL type path. It only works with Lettered
drives.

So I guess it would depend on what thisWorkbook.Path returns.

--
Regards,
Tom Ogilvy

Marcotte A said:
Thanks Ron. I'm looking at that site right now. Any idea why this code
works for a folder on my machine, but not for one on a network?

myPath = ThisWorkbook.Path
ChDrive myPath '.......... <--- generates error
ChDir myPath

"ThisWorkbook" is in the correct folder on the network. I would think that
"ThisWorkbook.Path" would give me the correct string to pass to ChDrive.
Click to expand...
 
ChDrive/ChDir don't work with UNC paths.

But Ron's suggestion of the API will work with UNC or mapped drives.

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

Sub testme()

Dim mySavedPath As String
Dim FileToOpen As Variant

mySavedPath = CurDir

On Error Resume Next
ChDirNet "\\xxxxx-s2\documents\Marcotte"
If Err.Number <> 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If

FileToOpen = Application.GetOpenFilename("Excel Files,*.xls")
ChDirNet mySavedPath

If FileToOpen = False Then
Exit Sub
End If

'do your stuff

End Sub
 
ThisWorkbook.Path returns a URL type path ("\\s2\documents\...).

Thank you to everybody for your help. I copied the relevant folder onto my
hard disk as a temporary fix for today, but will try Ron's and Dave's
suggestions going forward.

Tom Ogilvy said:
ChDrive doesn't work with a URL type path. It only works with Lettered
drives.

So I guess it would depend on what thisWorkbook.Path returns.
Click to expand...
 
Back
Top