Multi tool use

Clash Royale CLAN TAG

VBA: Excel 2013 autosave user desktop File Format Error

I have a product creation template, which filled by other users. I'd like to automatically save it to users desktop. I have to problems.

First one is I'd like to save file to users desktop with predefined name. I wrote a similiar code at my last job (excel 2010) in my new job we are using excel 2013 and file paths are different. So I have to extract users desktop path, they can use pc with other languages so I'm not sure whether below code will work.

CreateObject("WScript.Shell").specialfolders("Desktop")


Second problem is I want to save it as "xlsm" and when I changed the file extension to ".xlsm" it didn't save.

My old code which I used my last job is;

ActiveWorkbook.SaveAs Environ$("UserProfile") & "My Documents" &
Environ$("UserName") & " - " & Format(Date, "yyyy-mmm-dd") & ".xls"


thanks in advance

1 Answer
1

Have a look at the documentation of the Workbook.SaveAs Method and you will see that .SaveAs has a FileFormat parameter (also see XlFileFormat Enumeration).

.SaveAs

FileFormat

Accordingly you must use

ActiveWorkbook.SaveAs Filename:=Environ$("UserProfile") & "My Documents" & _
Environ$("UserName") & " - " & Format(Date, "yyyy-mmm-dd") & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.