Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel VBA SaveCopyAs and remove macros

Posted on 2011-10-12
8
Medium Priority
?
5,811 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:takwirirar
  • 4
  • 2
  • 2
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36956440
You cannot change the type with SaveCopyAs - you have to SaveAs if you want to change it.
0
 
LVL 1

Author Comment

by:takwirirar
ID: 36956466
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36956477
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:takwirirar
ID: 36956511
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 36956551
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
 
LVL 2

Expert Comment

by:ref-IT
ID: 36956739
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
 
LVL 2

Expert Comment

by:ref-IT
ID: 36956783
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36956787
@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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question