PDA

View Full Version : Method 'SaveAs' of object'_Workbook failed


SteveABC
11-21-2022, 12:18 AM
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/image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAjMAAAA7CAYAAACOnguGAAAGg0lEQVR4nO3dvW7i ShyG8ddHuRTYIsoVDFcANFS029klbrZLuV0aU LutKloYlcp8RWgRIp9DbmFOYVtsAHzkYTdjM7zk5A29ng8wEp 85 J7T2/vlsBAAA46p /PQAAAIDP8CRRmQEAAN/a8 t75z4qMwAAwGk39T spUADAAC F8/zTrahMgMAAJxGmAEAAE4jzAAAAKcRZgAAgNMuDDOpAs T5wVKJUmF5oPmzwAAAH/WgTBTB5T2KyCtAACAb ime5evxC40bG0bamGtFlceFAAAwLkunGY6Z1ppt7Iz0Lz45CgBAAA6fPEC4ELzQV9h5iuxVtZaJ X6msM aGgAAcB1Hwkys0aXVleJJj5kkf7KZnhpOfEmxlqQZAABwBReumTkhf1EmSfFIXvy5gQEAAJzjKv eZMVEuW00z1a/FRakIAADgPF8bZoYT ZKy8IE1MgAA4I/44srMUAubyG tt GmegAA4Ho8SVaSrLV/eSgAAABtnudJkp5f3zvb8GwmAADgNMIMAABwGmEGAAA4bXOfmXpOCgAAwCWbMMMCYAAA8N2cU2x hmgkAADiNMAMAAJzmVphJg/MfegkAAP4X9sJMGrTv2FvMB/I8T4NNgig0H3jygrQKF VdfoPmLX6LuQab7VV7b c1mGs/k6QKGm0G82LTV3DNWwg3xrt5XfWE56g/i/bdk vvY3 Mh9tf2k93/yf8ie8JAIAD9sJM/9ZIWuutShr5SyZJyl7yqkWul0wyt/3qZyNjpHi5vYqlD6EyY2QkST3NVlbW5oqMJBMpt1Z2NVOvdeZUgTdSXO 3uaaPO894Gi5k7Uqz9oFnKi/qgyNlnc0DMhNfin9fuQJ0ZDzFXANvqdvI7BwS6Kf lbVWia/tGLvaX9pP13YAAL6xvTDTG09llKnMLoXe1tWOeFkGi JNaxlNx9tEMZ362/1KtYwlfzq9bCTpUrEk/74OOT3NVgu1Hra989t/q4pQV3rSQJ4XKAia2wvNByPFkrKw31EVapxm86ZPnGcw1zzYf/5UGhyoQLXGNdDg2Hh6M63sQuPdgQ0XWlVJbjjxpfp76mp/aT9d2/c i3qqryuQtStsQXrucQAAXG5/zUzvh 5UVVqKJz1mRlHkq67WFE PynSnH83qyHgiX7GWqapQ4muydwU9rgwQvibDk01LaaB eKfEWlmbyM9C/dxcHGNpYmXzSCYL9ZD2NFsl8lVVX/aqQqUs7MvzPPVDKcqrCtCx82ShHm/z8jyK9XteSGmgUWwU5dvzN8cVK5G1K63OGM/Rt7 ML/u8Luyna3tvdi9fmcKHVGlQVtL 3SmVFfPfjQqb1WJ43nEAAHzEgQXAQ018Ses3FflLGVzGtzLK9PhUlNNO/qRdMVFft9VUU7qMD z/emX4qZ/OXVY5tow2s2AXMFEdTMr3es55puPeJgBmL3n5/s1U456k3lhT05yiM4p fcEnkwYaxZKfLD73OXf1c7T/oX5FRopHZZv7/SDW 3EnZaH6rcXap48DAOAjDv41U//WSNmLnt7WZTDZXJSf9LZurpep9TSebi9U /vPtV2rcx6/qpiUr9VX/Kbfm nel7KwuV7nCuf5qGKuwSiWiXItPpNkuvq5sP/1oS9suKjWSGUK 4enkw4eBwDABxwMM W6mVhhmFXBpKcfd5LiUGHWXi/TPkaSDu8/ZTMN8XO7fiQNdv8yp9G HJCWV/jrmXK9SNn38fNUFZziTWtJ/mRYBcFHPRWqpunK7V8jVdAPlfnJJwNVVz8d26u1SmUoSfUQZjJRosjshr66eaB50dNsVS36rvo dRwAAB9lJdm2xPrVdj pN/m2bOvbxDa3GRvl1lqb28jIykS2/DGypnn87v5DqmPqMZkob/ez02fib9tuxtocU1f73TFU7UxUb63ef3XgsfMYs99nq/325I3P6rzxaOezyCPT2rbpv6P9pf2c6n97bPV/oO6/0ab5PTX/v3QeBwDAEfX16Pn1vfPlVY14NtOl0kDeaL1dKAwAAL5c/Wym59f3zjZu3QEYAABgB5UZAADwbZ1TmbnZbQwAAOASppkAAIDTbo6VbQAAAL47KjMAAMBphBkA AOA0wgwAAHAaYQYAADiNMAMAAJxGmAEAAE4jzAAAAKcRZgAAgNMIMwAAwGmEGQAA4DTCDAAAcBp hBgAAOI0wAwAAnEaYAQAATiPMAAAApxFmAACA0wgzAADAaf8BJd1xFXpMgpUAAAAASUVORK5CYI I=


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
georgiboy
11-21-2022, 12:40 AM
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
SteveABC
11-21-2022, 02:30 AM
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")
georgiboy
11-21-2022, 02:48 AM
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
SteveABC
11-21-2022, 03:16 AM
No. Sorry. Just hits the same error message.
georgiboy
11-21-2022, 03:23 AM
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
SteveABC
11-21-2022, 03:55 AM
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.
georgiboy
11-21-2022, 04:06 AM
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?
SteveABC
11-21-2022, 04:47 AM
I'm on MS.

I pasted the text into explorer and the file opened as expected.
georgiboy
11-21-2022, 05:00 AM
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/office/vba/api/excel.xlfileformat
SteveABC
11-21-2022, 09:52 AM
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.



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/office/vba/api/excel.xlfileformat