Run-time error 52 in previously workable VBA code

[Adar123]

Hi Everyone,

The VBA code works with the files stored on shared drive. The path to the folders and files is defined in the VBA code as " \\server\path\folder with data\". (See extract of code below).

I have not had any issues with running VBA scripts by referencing this path before. However, today, when running a VBA script, I get a "run time error 52 bad file name or number" whenever the VBA code line contains reference to files or folders on the shared drive. The issue is solved if instead of "\\server\sharename\folder with data\" I write "Y:\path\folder with data\", where "Y" is the local machine drive name of this shared drive.

The VBA needs to be used on different machines where the local name of the shared drive may be different . So replacing the path with "Y:\path\folder with data\" does not seem like a workable solution. Is there any other ways to solve the issue?

Extract of VBA for reference (lines of code where error is thrown out are marked in bold).

Dim strFileName As String
Dim strFileExists As String

'Check the file name ending
strFileName = "\\server\path\folder with data\DataFile_" & dYes & "_" & dTod & "_000501UTC.zip"
[B]strFileExists = Dir(strFileName)[/B]

If strFileExists = "" Then     '.zip file to be unzipped
    localZipFile = ""\\server\path\folder with data\DataFile_" & dYes & "_" & dTod & "_000502UTC.zip"
Else
    localZipFile = ""\\server\path\folder with data\DataFile_" & dYes & "_" & dTod & "_000501UTC.zip"
End If
   
destFolder = ""\\server\path\second folder with data\"    'destination folder of .zip file's unzipped contents
   
'Unzip all files in the .zip file
       
Set Sh = CreateObject("Shell.Application")
With Sh
       
[B].Namespace(destFolder).CopyHere .Namespace(localZipFile).Items[/B]
End With

 

[jasonb75]

I only access the forum from home so I don't have access to suitable network drives for testing anything.

Searching for similar problems, I found this Open file on network directory
In that thread, there is a link to specific post in another which appears to be more useful, Find letter assigned to main drive

The reply posted there will most likely be specific to the question that was asked but hopefully there is enough information in the thread to make it useful for you.

 

[Adar123]

Thank you. Looks like over night my PC changed its mind and has no problem with reading this code this morning. I will save these solutions for later.