Excel VBA SaveCopyAs and remove macros

Hi Experts I have a line of VBA that saves a copy of my spreadsheet and emails it. I have the line of code

wb.SaveCopyAs TempFilePath & TempFileName & FileExtStr

How do I savecopyas and change the type i.e. the copy I am saving I do not want it to be a Macro Enabled spreadsheet?
LVL 1
takwirirarIT Projects ManagerAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Assuming you are using 2007 or later, you specify the FileFormat xlOpenXMLWorkbook as the second argument of saveas, and change the name to use .xlsx rather than .xlsm - for example:
Set wbk = Workbooks.Open("copy I just saved.xlsm")
wbk.SaveAs "copy I just saved.xlsx", xlOpenXMLWorkbook
wbk.close False

Open in new window


and then attach the xlsx copy to the email.
0
 
Rory ArchibaldCommented:
You cannot change the type with SaveCopyAs - you have to SaveAs if you want to change it.
0
 
takwirirarIT Projects ManagerAuthor Commented:
Problem is if I do a save as with Format 51 I cant then attach it to the email since CDO will not allow me to email a open workbook
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Rory ArchibaldCommented:
You'd need to do savecopyas, open the copy, saveas new format, then close it. You can then email the macro-free version, and delete both copies if required.
0
 
takwirirarIT Projects ManagerAuthor Commented:
The whole process needs to be done automatically so after opening the copy and saving it as a copy how would I email it without macros?
0
 
Mudasir NooraniSenior Analyst ProgrammerCommented:
Hello Takwirirar,

One way could be this way:

The two main file extentions for saving normal workbooks is either

.xls for 97 to 2003 format
and
.xlsx for 2007 Plus format

Therefore, depending on the Excel version you have, you can change the value of FileExtStr to either ".xls" OR ".xlsx" before the code above, so it would look something like this:

FileExtStr = ".xls" (for 97 - 2003 versions) OR FileExtStr = ".xlsx" (2007 Plus versions)
wb.SaveCopyAs TempFilePath & TempFileName & FileExtStr

Another way would be to add the file format enumerator after the file path. If you look in the help files (you could probably highlight the SaveCopyAs text in the VBA Editor and hit the F1 button, you'll find that the next parameter after the filename is the file format argument. The parameter for saving a Workbook as a default Workbook is xlWorkbookDefault (value 51) OR  as a normal Workbook is xlWorkbookNormal(value -4143)

Therefore your code could look something like the following:
Either
wb.SaveCopyAs TempFilePath & TempFileName & FileExtStr, xlWorkbookDefault
OR
wb.SaveCopyAs TempFilePath & TempFileName & FileExtStr, xlWorkbookNormal

Hope this helps.

ref-IT
0
 
Mudasir NooraniSenior Analyst ProgrammerCommented:
My apologies, the later part of the explanation can only apply to .SaveAs - please ignore the later part (The part where it says from "Another way would be to add the file format .....")

ref-IT
0
 
Rory ArchibaldCommented:
@ref_IT,
As I mentioned, SaveCopyAs does not allow you to change the file format, and simply altering the extension has no effect at all.

Regards,
Rory
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.