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.

http://www.vbaexpress.com/forum/imag...AASUVORK5CYII=

Code:

---

   
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

---

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:

Code:

---

WB.SaveAs filename:=FPath & FName & ".xlsx", FileFormat:=1

---

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

Code:

---

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")

---

If you run the below line within your code - does it give you a path that you can follow in the file explorer?

Code:

---

Debug.Print FPath & FName

---

No. Sorry. Just hits the same error message.

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?

Code:

---

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

---

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.

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?

I'm on MS.

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

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

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

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

---