Method 'SaveAs' of object'_Workbook' failed error

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
I'm having some trouble saving spreadsheets on a network drive. I've looked all over Google (on the sites my work laptop will allow me to access), and searched through Mr. Excel, but haven't found anything. I'm using a macro enabled document to open a .xlsx document, complete some fields, then I want to save the .xlsx document on a network drive, using the value of cell K7 as the file name.

VBA Code:
fPath = [URL='https://www.mrexcel.com/board/file://%5C%5CServer%5CFolder%5C']\\Server\Folder\[/URL]
fName = sMS1.Range("K7").Value & ".xlsx"
s.SaveAs fPath & fName

When I run the code, I'm getting a Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed error.

I'm not sure if the value in cell K7 having preceding zeroes could be the issue. Both fPath and fName are declared as string
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
Preceding zeroes or blanks? If blanks then why not trim it but blanks won't affect filename I think.

What was the error like. If the destination has existing filename, then it will also give error 1004
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
Preceding zeroes or blanks? If blanks then why not trim it but blanks won't affect filename I think.

What was the error like. If the destination has existing filename, then it will also give error 1004
They are zeroes, not blanks. The destination path is coded exactly as it appears in the directory.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You need to add the FileFormat in the code if saving as a different File type.
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

You need to add the FileFormat in the code if saving as a different File type.
The file being saved is .xlsx when it's opened, and is being saved as .xlsx in the code.
VBA Code:
fName = sMS1.Range("K7").Value & ".xlsx"
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
The macro is held in a xlsm. Try adding the fileformat to your code and see if it makes a difference.
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

The macro is held in a xlsm. Try adding the fileformat to your code and see if it makes a difference.
My apologies, but I'm not understanding what you mean when you say, "...adding the file format to your code...". I have the .xlsx format called out in the fName, but that apparently isn't what you're suggesting. Can you elaborate for me, as this is the only way that I've ever used to save files via VBA.
 

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
519
Office Version
  1. 2013
Platform
  1. Windows
I read through that article, and either I'm daft, or there's another issue. Here is the code in its entirety.
VBA Code:
Sub CreateStatements()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m, s As Workbook
Dim mCD, mTH, sMS1 As Worksheet
Dim mCDLR, mTHLR, sMS1LR As Long
Dim Rng, c As Range
Dim fPath, fName, fExtStr As String

Set m = ThisWorkbook
Set mCD = ThisWorkbook.Sheets("Core_Data")
Set mTH = ThisWorkbook.Sheets("Tran_Hist")
Set s = Workbooks.Open("\\Server\Folder\Template.xlsx", False)
Set sMS1 = s.Worksheets("MS1")

mCDLR = mCD.Range("A" & Rows.Count).End(xlUp).Row
mTHLR = mTH.Range("A" & Rows.Count).End(xlUp).Row

Set Rng = mCD.Range("A2:A" & mCDLR)

fPath = "\\Server\Folder\"
fExtStr = ".xlsx": FileFormatNum = 51

mTH.Activate
ActiveWorkbook.Worksheets("Tran_Hist").Sort.SortFields.Add Key:=Range( _
    "A2" & mTHLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
ActiveWorkbook.Worksheets("Tran_Hist").Sort.SortFields.Add Key:=Range( _
    "C2" & mTHLR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("Tran_Hist").Sort
    .SetRange Range("A1:E" & mTHLR)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

For Each c In Rng
    If c <> "" Then
        s.Activate
        sMS1.Range("A59:J90").ClearContents
        c.Copy
        sMS1.Range("K7").PasteSpecial xlPasteValues
        If sMS1.Range("G57").Value < 1 Then
        sMS1.Range("A59").Value = "No transaction activity for this period."
        fName = sMS1.Range("K7").Value '& ".xlsx"
        s.SaveAs fPath & fName & fExtStr
        's.Close 'New Line
        Else
            mTH.UsedRange.AutoFilter Field:=1, Criteria1:=sMS1.Range("K7").Value
            mTHLR = mTH.Range("A" & Rows.Count).End(xlUp).Row
            mTH.Range("C2:C" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
                sMS1.Range("A59").PasteSpecial xlPasteValues
            mTH.Range("D2:D" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
                sMS1.Range("D59").PasteSpecial xlPasteValues
            mTH.Range("E2:E" & mTHLR).SpecialCells(xlCellTypeVisible).Copy
                sMS1.Range("J59").PasteSpecial xlPasteValues
        End If

        fName = sMS1.Range("K7").Value '& ".xlsx"
        s.SaveAs fPath & fName & fExtStr 'It fails here
        's.Close 'New Line
    End If
Next

Application.DisplayAlerts = True
Application.DisplayAlerts = True

End Sub
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,170
Office Version
  1. 2016
Platform
  1. Windows
The syntax for using SaveAs is
workbook object .SaveAs(FileName, FileFormat, Password, WriteResPassword, _
ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, _
AddToMru,TextCodepage, TextVisualLayout, Local)

The FileFormat you can use number or name

In your case s.SaveAs fPath & fName, FileFormat:=<your choice>

No need extension I believe.

Unlike previous Excel .xls, if you do not pur FileFormat under certain condition it will still save with right extension but when opening you get warning saying file with wrong extension. I think this happened when you open xls file and saveas filename.xlsx without mentioning file format.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,750
Messages
5,638,129
Members
417,010
Latest member
jnuss03

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
Top