Solved: Getting macro to work on network and local drive ??

[nathan2314]

Hi,
I have a macro that I wrote that works fine if I'm running it on a drive that starts with a letter (ex. c:\ ... ). Basically, part of the code needs the current location of the Excel file that the macro is in for it to run properly. I have it working fine on a lettered drive with the snippet of code below.
[VBA]
MyCompletePath = ActiveWorkbook.FullName
MyPath = ActiveWorkbook.Path
SaveDriveDir = MyPath

ChDrive MyPath
ChDir MyPath[/VBA]

The problem is that once I send this macro out to others, some will run it on a network UNC path also (example \\networkpath\ ... ). Is there an easy way to modify my code to have it work no matter where a person runs it??
Appreciate your help!!!

[nathan2314]

Just to add more clarification, the error pops up when the code gets to ChDrive MyPath. Basically, the ChDrive function looks at the first character of MyPath and takes that as the lettered drive. But if it is run on a network, the first character is a "\" and so it gives an error. The macro needs to access of Excel files in the same folder it is and it saves to the same folder. So it needs to be able to find its location to work.

Thanks for any help!!

[Bob Phillips]

Why do you need to change drive, why not just access the files through the UNC?

[nathan2314]

If it will work on a local drive and unc path with just accessing through UNC then that will work. How do I do that?

[nathan2314]

Ok I tried to tinker with not using ChDrive and just using ChDir. It's not working. I was able to get the UNC path and create an array with all the .xls files in the folder where the excel macro sits. But later when I try to open one of these, Excel gives an error because it is looking in 'MyDocument' on my local computer for the files. How can I change my default drive path.
Here is the snippet of code that is supposed to reset the drive and directory so that everything is operating in whatever folder the Excel macro is in. It's not working though.
[VBA]
MyCompletePath = ActiveWorkbook.FullName
MyPath = ActiveWorkbook.Path
SaveDriveDir = MyPath

'ChDrive MyPath
ChDir MyPath

FNames = Dir(MyPath & "\*.xls*")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
'ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Do While FNames <> ""
If FNames <> "MasterCompile.xls" Then
Set mybook = Workbooks.Open(FNames)
If colorset = color1 Then
[/VBA]

There error pops up when the code gets to Set mybook=workbooks.open(FNames). It is looking for the correctly named file (Fnames was pointing to the correct folder with the Excel Macro and captured all the correct .xls) but when it tries to open the correctly named .xls file, it is looking on my default folder on my local drive. Is there an easy way to have it look in the folder where the Excel Macro is in??
Thanks for you help!

[Bob Phillips]

ChDir won't work either, but I think you can get away without that stuf

[vba]

MyCompletePath = ActiveWorkbook.FullName
MyPath = ActiveWorkbook.Path
SaveDriveDir = MyPath

FNames = Dir(MyPath & "\*.xls*")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
Exit Sub
End If
Do While FNames <> ""
If FNames <> "MasterCompile.xls" Then
Set mybook = Workbooks.Open(FNames)
If colorset = color1 Then
[/vba]

[nathan2314]

Blah that didn't work
It still gives the same error when it gets to Set mybook=workbooks.open(fnames). I'm currently testing it on a network. The varialbe MyPath is correctly getting the path \\lassen\....
I did a test and put a copy of the input Excel sheet into 'My Document's on my C: drive and reran it. It ran fine. So the problem is it keeps looking my "My Document' for the input Excel files and not in the folder where the Excel Macro is.
The ChDrive and ChDir work fine if I'm running from a drive. But when I move it to a UNC path it wont work.
Any suggestions?

[Bob Phillips]

There is an MS article on using Dir with UNC http://support.microsoft.com/kb/168519

[nathan2314]

Thanks for the link.
I actually figured a simple way around the problem. Not using any drive or directory functions at all and just changing the statement Set mybook=Workbooks.Open(Fnames) to Set mybook = Workbooks.Open(MyPath & "\" & FNames) solved the problem.

[Bob Phillips]

That was what I was getting to in my earlier suggestion, I missed the fact that the open had no path

[nathan2314]

Ahhh so you were right from the get go.
Thanks for your help!