dlh
08-01-2008, 01:36 AM
I'm trying to create a "snapshot" macro that creates a copy of the current workbook, pasting values in place of formulas and removing all VBA code & UserForms.
The challenge I'm facing is that I'm working in Excel 2007 but I need this macro to work equally well in Excel 2003. And all resulting files need to be readable in 2003, too. (I'm working partially blind since I cannot test anything with 2003.)
Ideally I'd just add a new workbook, insert the appropriate number of sheets, copy over everything I need by values, and save it. That way I wouldn't have to worry about figuring out how to write code which deletes itself. But in 2007, new workbooks default to a 2007 format, so the result wouldn't be readable by 2003. And if I wrote code to explicitly SaveAs in 2003 format, then the format identifier (a new feature in 2007) wouldn't be recognized and the code would break when run by Excel 2003.
I though I might use a shell command to duplicate my current file, open the duplicate file and strip code from that one, but there has to be an easier way. Right?
The challenge I'm facing is that I'm working in Excel 2007 but I need this macro to work equally well in Excel 2003. And all resulting files need to be readable in 2003, too. (I'm working partially blind since I cannot test anything with 2003.)
Ideally I'd just add a new workbook, insert the appropriate number of sheets, copy over everything I need by values, and save it. That way I wouldn't have to worry about figuring out how to write code which deletes itself. But in 2007, new workbooks default to a 2007 format, so the result wouldn't be readable by 2003. And if I wrote code to explicitly SaveAs in 2003 format, then the format identifier (a new feature in 2007) wouldn't be recognized and the code would break when run by Excel 2003.
I though I might use a shell command to duplicate my current file, open the duplicate file and strip code from that one, but there has to be an easier way. Right?