Workbooks.Open using network path

[Jow]

Here's the code I'm using...It basically opens up each excel file inside a folder one at a time, and runs code within each file.

Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "\\Server-1\production_share\Production Bucket Folder\"
ChDir MyPath
TheFile = Dir("*.csv")
 
 
Do While TheFile <> ""
  Set wb = Workbooks.Open(MyPath & "\" & TheFile)
 
 
*CODE HERE*
 
 
  wb.Close SaveChanges:=True
  TheFile = Dir
Loop

The problem is that it doesn't work when I'm using a network path (as above), it will only work when the path I'm using is on a local drive.

For example, when I change 'Mypath' to -
"C:\Users\ianlane\Desktop\TESTING BUCKET FOLDER"
it works fine.

I have tried using a mapped network drive, but this doesn't work either, it still won't work.

Later on in my code I use the same network path to copy the contents of one folder to another, and it works perfectly for this purpose, but not with workbooks.open -


Is this a known issue? Any ideas on how I can get around it?

[DoLoop]

Replace :

[vba]MyPath="\\Server-1\production share\Production Bucket Folder\ "[/vba]

With:

[vba]MyPath="\\Server-1\production share\Production Bucket Folder"[/vba]

Or you can change your code into a little bit easier one:

[VBA]Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "\\Server-1\production share\Production Bucket Folder\"
TheFile = Dir(MyPath & "*.csv")


Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & TheFile)


*CODE HERE*


wb.Close SaveChanges:=True
TheFile = Dir
Loop
[/VBA]

[Jow]

Yeah at first I suspected it was a problem with the backslash too, But it doesn't seem to make a difference.

The original block of code I posted works with or without the extra backslash, as long as I'm using a local directory.

So that means it will work for a local path but not a network one...as far as I can see anyway...



Any other suggestions?

[DoLoop]

Have you tried to change the network path to another one (another server)?

[Jow]

We only have the one server, I have tried running it from different PC's attempting to open workbooks at different locations on the server but it still doesn't work.

Only works if I'm running the code on files that are stored on local drives...

[DoLoop]

Try this


[vba]

Dim wb As Workbook

Dim TheFile As String
Dim MyPath As String
MyPath = \\Server-1\production_share\Production Bucket Folder\
On Error Resume Next
TheFile = Dir(MyPath & "*.csv")
If Err then
debug.print Err.Description
Exit sub
End if
On Error Goto 0

Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & TheFile)

*CODE HERE*

wb.Close SaveChanges:=True
TheFile = Dir
Loop

[/vba]

And look at your immediate window in VBE (Ctrl+G) what the error description was. Are you sure that the network drive can be accessed? Or that there is no kind of security policy on it for VBA app's?

If something's wrong, blame IT

[Jow]

There were no error messages actually, the code just didn't work.

I've just tried the 'on error resume next' as you suggested, and it worked! So, I'm guessing it must be random network activity that was causing it to fail?

It seems to be running without issue now, thanks!