Excel 2007 erases VBA code from Excel 2003

[Andrew]

Hello,
For the past two years, I've been using a purchase order generator
which I made in Excel VBA. The main file is a template with two
sheets. Sheet 1 is the purchase order, and sheet 2 is a list of all
my contacts. Sheet 1 has two buttons, one to update my contact list
and one to create a PO and save it to a specified folder... So, when I
hit the button to update the contacts, I get a dialog box which asks
for name, address, etc. On that dialog box there is another button
which is supposed to update the contact list and then save the entire
file as the new template. So, of course the new template will have
all of the VBA code. This system worked well for 2 years is Excel
2003. Last week I updated to Excel 2007 and when I updated my contact
page, the resulting template was saved but all of the VBA code was
gone. So, essentially the template is destroyed. I tried this again
with another copy of the file and the same thing happened.

Can someone explain to me why Excel 2007 is erasing my VBA code? And
what can I do to prevent this?

thanks

 

[Jarek Kujawa]

Excel 2007 uses 2 file formats
..xlsx (no macros/code)
..xlsm (containing macros/code)

seems like you are saving yr template in .xlsx format
which should be saved in .xlsm

 

[Andy]

Excel 2007 uses 2 file formats
.xlsx (no macros/code)
.xlsm (containing macros/code)

seems like you are saving yr template in .xlsx format
which should be saved in .xlsm

Actually, I have set the default to save as *.xls. But I'm not sure
that this applies to saving templates. I'm certain that my code used
to save as *.xlt. Where would I find these settings in Excel 2007?

 

[Jarek Kujawa]

I might have been missing sth.
but there should be an .xltm format in yr SaveAs window (Save as type)
sorry, am using a Polish version of Excel 2007
or the solution is totally different

 

[dennis.mccarthy]

Hello,
For the past two years, I've been using a purchase order generator
which I made in Excel VBA.  The main file is a template with two
sheets.  Sheet 1 is the purchase order, and sheet 2 is a list of all
my contacts.  Sheet 1 has two buttons, one to update my contact list
and one to create a PO and save it to a specified folder... So, when I
hit the button to update the contacts, I get a dialog box which asks
for name, address, etc.  On that dialog box there is another button
which is supposed to update the contact list and then save the entire
file as the new template.  So, of course the new template will have
all of the VBA code.  This system worked well for 2 years is Excel
2003.  Last week I updated to Excel 2007 and when I updated my contact
page, the resulting template was saved but all of the VBA code was
gone. So, essentially the template is destroyed.  I tried this again
with another copy of the file and the same thing happened.

Can someone explain to me why Excel 2007 is erasing my VBA code?  And
what can I do to prevent this?

thanks

You can try saving it as an Excel Macro Enabled Template - .XLMT.
You should also check the settings under Excel Options Save - this
should be set at .XLSX.
We had the same problems changing over - basically you can loose the
VBA code when it saves from 2007 back to 2003 versions of excel

 

[Andy]

You can try saving it as an Excel Macro Enabled Template - .XLMT.
You should also check the settings under Excel Options Save - this
should be set at .XLSX.
We had the same problems changing over - basically you can loose the
VBA code when it saves from 2007 back to 2003 versions of excel

Dennis,
So, you lose the code when saving from 2007 to 2003? The code began
as 2003, and I've been trying to save everything in the xls/xlt format
just to avoid all of the security settings in 2007. So, if I save as
a 2007 template, will this alleviate the problem? What did you guys
do to fix it.

thanks