Consulting

Results 1 to 11 of 11

Thread: Method 'SaveAs' of object'_Workbook failed

  1. #1
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location

    Method 'SaveAs' of object'_Workbook failed

    Hello.

    I keep getting the above (title) error message on the below line of code. I've tried a few alternatives but can't seem to get past this error message. Can't think for the life of me what's going wrong. I'm thinking the problem stems from the file name in O6.

    File name in Range O6. See Attached.


        
    Dim formattedCurrentDate As String
    formattedCurrentDate = Format(Date, "dd-mm-yyyy")
    Dim FName As String
    Dim FPath As String
    Dim Newbook As Workbook
    Dim filename As String
    Dim WB As Workbook
    ActiveSheet.Unprotect
    Sheets("SUMMARY").Select
    ActiveSheet.Unprotect
    Sheets("TOTAL WEEK").Select
    ActiveSheet.Unprotect
    Sheets(Array("SUMMARY", "TOTAL WEEK")).Select
    Sheets(Array("SUMMARY", "TOTAL WEEK")).Copy
    Sheets("SUMMARY").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("TOTAL WEEK").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    FPath = "Q:\Finance\Accounts\Finance\CASH, BANK and LOANS\Client Account Reconciliations\Solutions Client accounts\TOCS\Client Reports\WMT\Client Reports"
    FName = Range("O6")
    Set WB = ActiveWorkbook
    WB.SaveAs filename:=FPath & FName & ".xlsx", FileFormat:=1
    WB.Close
    Attached Images Attached Images

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Looks like you are missing a \ from the end of FPath

    Also if the FName has '.xlsx' then you don't need it in the below line:
    WB.SaveAs filename:=FPath & FName & ".xlsx", FileFormat:=1
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location

    Method 'SaveAs' of object'_Workbook failed

    Ok, amended as per your instructions, but the problem remains.

    Dim formattedCurrentDate As String
    formattedCurrentDate = Format(Date, "dd-mm-yyyy")
    Dim FName As String
    Dim FPath As String
    Dim Newbook As Workbook
    Dim filename As String
    Dim WB As Workbook
    ActiveSheet.Unprotect
    Sheets("SUMMARY").Select
    ActiveSheet.Unprotect
    Sheets("TOTAL WEEK").Select
    ActiveSheet.Unprotect
    Sheets(Array("SUMMARY", "TOTAL WEEK")).Select
    Sheets(Array("SUMMARY", "TOTAL WEEK")).Copy
    Sheets("SUMMARY").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("TOTAL WEEK").Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    FPath = "Q:\Finance\Accounts\Finance\CASH, BANK and LOANS\Client Account Reconciliations\Solutions Client accounts\
    TOCS\Client Reports\WMT\Client Reports\"
    FName = Range("O6")
    'FName = "WMT Client Report " & Sheets("INPUT").Range("Q1").xlsx
    '"West Midlands Trains Revenue Report: " & Sheets("Input").Range("Q1")
    'FName = "WMT Client Report " & Format(Date, " ddmmyy") & ".xlsx"
    Set WB = ActiveWorkbook
    WB.SaveAs filename:=FPath & FName, FileFormat:=1
    WB.Close
    Range("Timestamp2").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")

  4. #4
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    If you run the below line within your code - does it give you a path that you can follow in the file explorer?

    Debug.Print FPath & FName
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  5. #5
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    No. Sorry. Just hits the same error message.

  6. #6
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    That suggestion was not suposed to fix the problem - it was supposed to help you study the filepath that is being created for potential errors. If you run the below, check the created file path - copy it from your Immediate window and paste it into the file explorer and see if it takes you to the file?

    Sub test()    
        Dim FName As String
        Dim FPath As String
        
        FPath = "Q:\Finance\Accounts\Finance\CASH, BANK and LOANS\Client Account Reconciliations\Solutions Client accounts\"
        FName = Range("O6").Value
        
        Debug.Print FPath & FName
        'MsgBox FPath & FName
    End Sub
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  7. #7
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    I checked the path, and it works fine.

    When you say takes you to the file? Do you mean takes you to the folder? I want to save the filename as Range 06 within that file path.

  8. #8
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    When you paste the text in the immediate window into the file explorer it should open the file if the path contains a path and file name with extension.

    For example:
    Q:\Finance\Accounts\Finance\CASH, BANK and LOANS\Client Account Reconciliations\Solutions Client accounts\WMT Client Report 211122.xlsx
    Should open the file if it exists.

    If it does not open the file then there could be something wrong in the way the file path or name are being created.

    Are you on MAC or MS?
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  9. #9
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    I'm on MS.

    I pasted the text into explorer and the file opened as expected.

  10. #10
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Have you tried file format 51?

    Could there be any protection on the folder you are writing to that may prevent you saving a file there?

    Does it work with an alternate file path?

    Sorry for all of the questions - it's the way my brain works.

    https://learn.microsoft.com/en-us/of...l.xlfileformat
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  11. #11
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    There's no protection on this path

    I amended the path to Q:\Finance\

    but error message remains the same

    File format changed to 51 but still same error message. Any other ideas? It's really bothering me now.


    Quote Originally Posted by georgiboy View Post
    Have you tried file format 51?

    Could there be any protection on the folder you are writing to that may prevent you saving a file there?

    Does it work with an alternate file path?

    Sorry for all of the questions - it's the way my brain works.

    https://learn.microsoft.com/en-us/of...l.xlfileformat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •