Run-time error 52 in previously workable VBA code

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
83
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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).
VBA Code:
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top