Error message using Next Invoice Number - Podcast 1505

[Jim885]

Hi,
I have Excel 2010, and I'm getting an error message when I press the Clear and save button while using this invoice program.

The message is: The following features cannot be saved in macros-free workbooks - VB Project.
To continue saving as a macro-free workbook, click yes.

I have the option of YES or NO to save the file, but I want to get rid of this message.

If I click YES, a new (macro-free) file is saved to my DOCUMENTS folder as a sequenced file number.

If I click NO, I then get "run-time error 1004" message, which then leads me to debug the code.

The code I have is:

Dim NewFN As Variant
' Copy to a new workbook
ActiveSheet.Copy
NewFN = "C:\Users\Jim\Documents\OrderSheetJim" & Range("P4").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextOrder
End Sub


The line that is highlighted by excel (and needs to be debugged) is:
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook

What is wrong?
Please help

 

[MARK858]

Try

Dim NewFN As Variant
' Copy to a new workbook
ActiveSheet.Copy
NewFN = "C:\Users\Jim\Documents\OrderSheetJim" & Range("P4").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
NextOrder
End Sub

Or if using with previous versions

Dim NewFN As Variant
' Copy to a new workbook
ActiveSheet.Copy
NewFN = "C:\Users\Jim\Documents\OrderSheetJim" & Range("P4").Value & ".xls"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlExcel8
ActiveWorkbook.Close
NextOrder
End Sub

 

[Jim885]

Thanks, but that doesn't exactly fix my situation. While both your suggestions work, the first suggestion saves the new file as macro enabled, which I don't want.

The second suggestion also works, but the new file is saved as an Excel 97 file. When I try to open the new 97 version of the file, I get a message that warns me that the file is of a different version.

Any other ideas?

 

[MARK858]

You can't save a workbook in 2010 without it being in a xlsm or xlsb. You still have to manually enable the macro when you open the file if you have your security settings set to "with notification".
You can't save as xlsx and have the macros work.

 

[MARK858]

Just for further clarification below is an excerpt from MSDN. A link to the full article is below.

http://msdn.microsoft.com/en-au/library/aa338205.aspx#office2007aboutnewfileformat_introduction

Macro-Enabled Files vs. Macro-Free Files

Default 2007 Microsoft Office system documents saved in Office XML Formats are intended to be macro-free files, and therefore cannot contain code. This behavior ensures that malicious code, residing in a default document, can never be executed unexpectedly. While 2007 Microsoft Office system documents can still contain and use macros, the user or developer must save these documents as a macro-enabled document type. This safeguard will not affect your ability to build solutions, but allows organizations to use documents with more confidence.
Macro-enabled files have the exact same file format as macro-free files, but contain additional parts that macro-free files do not. The additional parts depend on the type of automation found in the document. A macro-enabled file that uses VBA contains a binary part that stores the VBA project. Any Excel workbook that uses Excel 4.0–style macros (XLM macros) or any PowerPoint presentation that contains command buttons are also saved as macro-enabled files. If a code-specific part is found in a macro-free file, whether placed there accidentally or maliciously, the Office applications will not allow the code to execute—without exception.
You can now determine if any code exists within a 2007 Microsoft Office system document before opening it. Previously this "advance notice" was not something that could be easily accomplished outside Office. You can inspect the package file for the existence of any code-based parts and relationships without running Office and without running potentially risky code. If a file looks suspicious, you can remove any parts capable of executing code from the file, so that the code can cause no harm.
​

File Name Extensions

2007 Microsoft Office system documents saved by using Office XML Formats have new file name extensions that allow Office to differentiate these file format documents from binary documents used by previous Office versions. The new extensions borrow from the existing binary file name extensions by appending a letter to the end of the suffix. The default extensions for documents created in Word 2007, Excel 2007, and PowerPoint 2007 using the new file formats append the letter "x" and are .docx, .xlsx, and .pptx, respectively. Other Office document types that use the new file formats (including templates, add-ins, and PowerPoint shows) also receive new extensions.
Another new change introduced in the 2007 Office release is that there are different extensions for files that are macro-enabled versus those that are macro-free. Macro-enabled documents include a file name extension that ends with the letter "m" instead of an "x." For example, a macro-enabled Word 2007 document has the .docm extension, and thereby allows any users or software applications, before a document opens, to identify that it contains code.
​

 

[Jim885]

Thanks for the information.
Your information helps.
My goal is to create and save the new file as a macro-free file, while maintaining the original file as macro-enabled ... and without receiving error messages.
It seems that if I turn off the "notification" option, I will have what I need.
Thanks again.