Runtime Error

[Holley]

Hello all!

I have a code that runs beautifully "most of the time", however, it will not always overwrite the file 100% of the time. I randomly receive a Runtime Error 1004 Method 'SaveAs' of object'_Workbook' fialed. If I rename or delete the file it works fine.

 ActiveWorkbook.SaveAs fileName:="\\cd22.abcnet.abc.com\share$\DESK\Report\29sIs.xlsx", FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=True

I have tried to add code to rename the file but get a Run-time error 75, Path/File access error at the Name oldName As newName line.

Dim oldName As String
Dim newName As String
oldName = "\\cd22.abcnet.abc.com\share$\DESK\Report\29sIs.xlsx"
newName ="\\cd22.abcnet.abc.com\share$\DESK\Report\29sIsBU.xlsx"
Name oldName As newName

I've even tried using the Kill code to delete the file prior to saving and get a Runtime error 70 Permission denied. The file is not open anywhere and I have read and write access so I'm not sure whats preventing the overwrite, delete or renaming.

Any suggestions would be most appreciated!

 

[Candyman8019]

I'm wondering if a delay in connecting to that drive may be causing your occasional issue? I would try mapping a drive to "\\cd22.abcnet.abc.com\share$\DESK\" then reference the file as "N:\Report\29sIs.xlsx" for example.

 

[Holley]

I'm wondering if a delay in connecting to that drive may be causing your occasional issue? I would try mapping a drive to "\\cd22.abcnet.abc.com\share$\DESK\" then reference the file as "N:\Report\29sIs.xlsx" for example.

Thanks!! I'll try that and let you know!!