ChDrive error (server path)
 

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

ChDrive error (server path)
 

Hi Marcotte

See
http://www.rondebruin.nl/copy3.htm

Every second macro of each example will also work for a network path

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


"Marcotte A" wrote in message ...
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

ChDrive error (server path)
 

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.

"Ron de Bruin" wrote:

Hi Marcotte

See
http://www.rondebruin.nl/copy3.htm

Every second macro of each example will also work for a network path

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

ChDrive error (server path)
 

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" wrote in message ...
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.

"Ron de Bruin" wrote:

Hi Marcotte

See
http://www.rondebruin.nl/copy3.htm

Every second macro of each example will also work for a network path

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

ChDrive error (server path)
 

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" wrote in message
...
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.

"Ron de Bruin" wrote:

Hi Marcotte

See
http://www.rondebruin.nl/copy3.htm

Every second macro of each example will also work for a network path

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

ChDrive error (server path)
 

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


Marcotte A wrote:

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.

"Ron de Bruin" wrote:

Hi Marcotte

See
http://www.rondebruin.nl/copy3.htm

Every second macro of each example will also work for a network path

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


--

Dave Peterson

ChDrive error (server path)
 

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" wrote:

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" wrote in message
...
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.

"Ron de Bruin" wrote:

Hi Marcotte

See
http://www.rondebruin.nl/copy3.htm

Every second macro of each example will also work for a network path

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