Excel macro issue - Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed when trying to save file via macro

[HoosierDaddy311]

Hello,

I am posting this question to the forum after reviewing several other posts trying to find a solution with no success.

OS - Windows XP
Office version - Office 2010

What the issue is - I have developed an Excel 2010 template with macro's (.xltm) that has some sales data pasted into it, and then that data is processed through a "workflow" within the template. Everything works just fine until the very final macro called "Finalize_And_Save_For_PDC_to_Upload" that dynamically saves the file using a filename that is created in another cell (I1) as a variable (FilenameValue) which is a formula that grabs text portions from other cells and strings them together to create a standard file-naming convention. Also used in the path to be saved in is another
week number variable that is in cell (I3) which is basically a =MID formula pulling the 2 digit week # from another cell. The final piece of this is when the macro below is run.

What the issue is - only SOMETIMES the Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed error is thrown, but NOT ALL THE TIME. This is the point of confusion as if it failed ALL THE TIME, I can understand there is something inherently wrong with the code, but as I mentioned sometimes it works just fine, while other times it fails with the '1004' error code. I have 40+ users using this template and this morning I had a user that was able to run it once just fine, but the next one run threw the error and there is nothing I can see that is different (aside from the filename).

Here is complete VB code being used with the portion of the code bold & italicized that is highlighted in yellow upon clicking the Debug button on the error code window:

Sub Finalize_And_Save_For_PDC_to_Upload()
'
' Finalize_And_Save_For_PDC_to_Upload Macro
'
Dim FilenameValue As String
Dim FilenameWeekNo As String

Sheets("Data").Select

ActiveSheet.ShowAllData

Application.DisplayAlerts = False

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("DND List", "40% Reduced Allocation Stores", "Store ROG", "CIC's", _
"Store's", "$5 Friday DND List", "$5 Friday Store List", "New Store & Model Store" _
)).Select
Sheets("DND List").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets(Array("DND List", "40% Reduced Allocation Stores", "Store ROG", "CIC's", _
"Store's", "$5 Friday DND List", "$5 Friday Store List", "New Store & Model Store" _
)).Select
Sheets("$5 Friday Store List").Activate
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("Cover Sheet").Select

Range("F2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

FilenameValue = Range("I1")
FilenameWeekNo = Range("I3")

ActiveWorkbook.SaveAs Filename:= _
"\\pleefs01\Data\Orion\Business Tracker Scan\2013\WK " & FilenameWeekNo & "\" & FilenameValue & ".xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False

Sheets("Pivot Summary").Select
ActiveWindow.SelectedSheets.Visible = False

Application.DisplayAlerts = True
End Sub

When it errors out and before the user clicks the Debug button, I can see the window name of the file contains the filename the macro created, however upon clicking Debug and seeing the bold & italicized portion of the macro highlighted in yellow, once the user closes the VBA window, the filename reverts back to the name it was upon opening the template (which is MEDS Allocation Template1 - Microsoft Excel)

Why would this VBA work sometimes but not others? Is there something I need to change to ensure it WORKS EVERY TIME it's run? Any help would be greatly appreciated!

Thanks for reviewing this post and I look forward to any possible solutions out there!

 

[Michael M]

Hi and welcome to the Board
couple of questions
With 40+ users, is it gooing to a server....and are they all on the same server ?
Are all users using the same version of EXcel ?
Have you tried using the complete directory path in the code.....eg,
ActiveWorkbook.SaveAs Filename:= " G:\pleefs01\Data\Orion\Business Tracker Scan\2013\

Also, shortened the code up some....try it on a test wrokbook, as I don't have EXcel at the moment, so it's UNTESTED.

Sub Finalize_And_Save_For_PDC_to_Upload()
' Finalize_And_Save_For_PDC_to_Upload Macro
Dim FilenameValue As String
Dim FilenameWeekNo As String
ActiveSheet.ShowAllData
Application.DisplayAlerts = False
    With Sheets("Data").UsedRange
        .Value = .Value
    End With
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Array("DND List", "40% Reduced Allocation Stores", "Store ROG", "CIC's", _
"Store's", "$5 Friday DND List", "$5 Friday Store List", "New Store & Model Store")).SelectedSheets.Delete
    With Sheets("Cover Sheet").Range("F2")
        .Value = .Value
    End With
FilenameValue = Range("I1")
FilenameWeekNo = Range("I3")
ActiveWorkbook.SaveAs Filename:= _
"\\pleefs01\Data\Orion\Business Tracker Scan\2013\WK " & FilenameWeekNo & "\" & FilenameValue & ".xlsx", FileFormat _
:=xlOpenXMLWorkbook, CreateBackup:=False
Sheets("Pivot Summary").SelectedSheets.Visible = False
Application.DisplayAlerts = True
End Sub

 

[HoosierDaddy311]

Michael - thanks for your quick reply - to answer your questions:

Yes - it's going to a server network drive (shared) that all 40+ users are on.
Yes - I confirmed that all users are on the same Excel version (2010)
No - I have NOT tried hard-coding the drive letter per your suggestion as even though all users are able to save to this shared drive, some of them have them mapped as different drive letters (some are G:\, some are P:\ while some others are U:\ mapped). This why I used the server name \\pleefs01\ name instead of the drive letter.

I will try this new code this evening at home and test it out! I will post another reply to this thread tomorrow and hopefully be able to close it. I mentioned this issue to a VBA guru here at work and asked him to take a look at it tomorrow if I couldn't find a solution so I'm hopeful your new code will work (less the drive letter mapping portion of the network drive). I believe I could use the actual drive letter but that would require re-mapping all users to one drive letter (which although possible is quite time consuming) not to mention they might already have shortcuts mapped to shared drive files/folders using whatever drive letter they are mapped to now.

Thanks again!

 

[addankirao]

Hi, I also have the same issue when trying to save the excel file into a shared folder where path is hardcoded. Please let me know if anyone has any solution. Thank you. Regards Raghav

 

[SidKad]

Hi hd311... Were you able to resolve this issue? If yes what was the solution.

 

[GingerBollix]

I am new to Macros, I've recently taken over a position where my predecessor had written a suite of Macros for the company.
The above problem is causing me major problems, I am almost certain that the root cause of the problem is the fact that Office 2010 creates a temporary cache file for everything it does in Excel but I don't have the knowledge to fix this myself.
I have scoured the internet and this problem has popped up many times but a generic solution has never been offered.
Every time the problem has been related to legacy Macros being used in Office 2010.