Method 'SaveAs' of object'_Workbook' failed error

[reberryjr]

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.

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

 

[Zot]

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]

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]

You need to add the FileFormat in the code if saving as a different File type.

 

[reberryjr]

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.

fName = sMS1.Range("K7").Value & ".xlsx"

 

[MARK858]

The macro is held in a xlsm. Try adding the fileformat to your code and see if it makes a difference.

 

[reberryjr]

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.

 

[MARK858]

Have a read of the article below which explains how to use it clearer and in more detail than I can.

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

 

[reberryjr]

I read through that article, and either I'm daft, or there's another issue. Here is the code in its entirety.

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]

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

XlFileFormat enumeration (Excel)

Office VBA reference topic

docs.microsoft.com

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.