Office 365 Excel 1902 Version Error 1004

[RompStar]

Hi guys!

long time no post! I was asked to do something at work that I havn't done in a while, but luckily I saved the VBA script, its a script that matched different workbook sheet store numbers and exports them to their corresponding network folders, luckily most of the script still works but I am hitting a wall on what previously worked in Excel 2003.

I have a Case lookup that will look up the network folder based on the sheet store number or value, so it looks it up and when it finds a store number, it tries to save to the network folder...

The problem is that sometimes people delete folders on the network drive, so when it tries to save it, it produced the 1004 error.

I have written a Error Handling that worked in Excel 2003 but now not, let me explain:

On Error GoTo ErrHandler

So when the Save attempt fails with Error 1004, it goes to the ErrHandling part of the script:

There I have basically instruction to save the file on my local drive, I just want to handle these, so that later I can resolve them, so that the process doesn't error out.

But now when it tries to Save again to a different location, that workbook is locked and no matter what, it won't work, so just wondering if I need to change that part of the code ?

I created the StoreDir2 for sure and made sure to double check it for spelling errors and it exists proper and I have access to it...

StoreDir2 = "c:\Users\LAN\Desktop\error1004"

If Err.Number = 1004 Then
ActiveWorkbook.SaveAs StoreDir2 & "Store_" & ws.Name & endoffilename & ".xls" ' <------------- script fails here
pg.Sheets("Call or Address List Guidelines").Copy Before:=Workbooks("Store_" & ws.Name & endoffilename & ".xls").Sheets(1)
x = x + 1
mylog(x) = StoreDir2 & "Store_" & ws.Name & endoffilename & ".xls"
ActiveWorkbook.Close True
Resume 1
Else
MsgBox "The message text of the error is: " & Error(Err)
End If


Thanks!

 

[RompStar]

... and here is StoreDir2 as you showed it in post #1 :

StoreDir2 = "c:\Users\LAN\Desktop\error1004"

there is no path separator at the end of it and you substituted LAN for ID.

there is a path separator, but for whatever reason, this forum removes it, try posting something and see for your self, and yes I put ID in for security reasons in the post, but real LANID in the code and I coded a test variable to assemble the entire string and I looked at it and it was fine, it was all there.