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:
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?
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:=FalseLater, 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?
