Save a Workbook generated by a template as .xlsx, then keep going.

GrumpyOldGit

New Member
Joined
Jun 28, 2012
Messages
13
Hi....

I have a requirement to be able to use a Template to allow the User to complete a Workbook, then save the Workbook in two different places all under VBA control.
The User enters info into the spreadsheet, and "behind the scenes" (i.e. in Macro code) the single Worksheet has sensitive information placed in it. Eventually, the Workbook may be E-mailed to a Customer, so the current solution is to intercept the save in Workbook_BeforeSave to give the User the options to 1)Cancel the save, 2)Continue, and eventually delete some of the sensitive information, or 3) Continue, and delete all of the sensitive information.
Regardless of the User's choice (except for Cancel), the workbook will be saved "as is" in a subfolder in the User's chosen save location (created by the VBA if need be). Then, depending on the User's choice, VBA code is executed to remove some/all of the sensitive information, and the save operation continues.

I'm using the following code to execute the saves:

Code:
strPFPPathAndName = "C:\Test\MacEnabled.xlsm"
 ActiveWorkbook.SaveAs Filename:=strPFPPathAndName, _
                       Password:="", _
                       WriteResPassword:="", _
                       ReadOnlyRecommended:=False, _
                       FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                       CreateBackup:=False

Later, I change contents of the variable holding the path and name to be "C:\Test\NormalDoc.xlsx", and change the FileFormat operand to be xlWorkbookNormal, and execute a save using similar code.

It seems to work, in that the spreadsheets get saved in the correct directories under the correct names, and no errors are flagged.

However, when I try to open the files which I have saved, I get a message telling me the files are corrupt and cannot be opened.

I can't figure out what I'm doing wrong. I would have thought that if I were trying to save a file with an incompatible extension, I would have been notified at the time I saved it.

Any ideas, guys?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,327
Members
414,053
Latest member
Dual Showman

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