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!

 

[Joe4]

What version of Excel are you using now?
You may need to change your file name to use an "xlsx" or "xlsm" (if it has macros) extension now, and you may also need to use the optional FileFormat argument.
See: https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

 

[RompStar]

Hi Joe4,

I am using Office 365, Excel version 1902 / how does the optional FileFormat argument work ?

 

[RompStar]

Hi Joe4,

I am using Office 365, Excel version 1902 / how does the optional FileFormat argument work ?

here is what I have tried and still that error, note also Tried .xlsx with FileFormat:=51

If Err.Number = 1004 Then
ActiveWorkbook.SaveAs StoreDir2 & "Store_" & ws.Name & endoffilename & ".xlsm", FileFormat:=52

pg.Sheets("Call or Address List Guidelines").Copy Before:=Workbooks("Store_" & ws.Name & endoffilename & ".xlsm").Sheets(1)
x = x + 1
mylog(x) = StoreDir2 & "Store_" & ws.Name & endoffilename & ".xlsm"
ActiveWorkbook.Close True
Resume 1
Else
MsgBox "The message text of the error is: " & Error(Err)
End If

 

[JoeMo]

If StoreDir2 is defined as you show it in post #1 , you seem to be missing a path separator.

Try:

ActiveWorkbook.SaveAs StoreDir2 & Application.PathSeparator & "Store_" & ws.Name & endoffilename & ".xlsm", FileFormat:=52

 

[RompStar]

If StoreDir2 is defined as you show it in post #1 , you seem to be missing a path separator.

Try:

ActiveWorkbook.SaveAs StoreDir2 & Application.PathSeparator & "Store_" & ws.Name & endoffilename & ".xlsm", FileFormat:=52

here is StoreDir2

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

I checked and I can access this path fine on my Windows 10 workstation

??

 

[RompStar]

so weird, I put the path directly without using the Variable and it worked fine:

ActiveWorkbook.SaveAs "c:\Users\ID\Desktop\error1004\ " & "Store_" & ws.Name & endoffilename & ".xlsx", FileFormat:=51

 

[RompStar]

and now it stopped working, without changing anything FTW!!!

 

[RompStar]

and now it stopped working, without changing anything FTW!!!

I wonder if the Firewall software is doing something weird, I will look into Windows Defender...

 

[JoeMo]

here is StoreDir2

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

I checked and I can access this path fine on my Windows 10 workstation

??

... 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.