Error message using Next Invoice Number - Podcast 1505

Jim885

Well-known Member
Joined
Jul 8, 2012
Messages
663
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try

Code:
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

Code:
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
 
Last edited:

Jim885

Well-known Member
Joined
Jul 8, 2012
Messages
663
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Well-known Member
Joined
Jul 8, 2012
Messages
663
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,482
Messages
5,601,920
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top