Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get the true path to server (not mapped drive letter)? | Excel Worksheet Functions | |||
Indirect does not recognize full server path? | Excel Worksheet Functions | |||
Excel 2003 Error Cannot Locate the Internet Server or Proxy Server | Excel Discussion (Misc queries) | |||
How do I use a server name in an Excel chart file path? | Charts and Charting in Excel | |||
Problems with ChDrive and ChDir | Excel Programming |