SaveAs XLSM question

[Barb Reinhardt]

I'm trying to save a workbook as a .xlsm in Excel 2007 using code.

I find that this works

ThisWorkbook.SaveAs Filename:=filesavename, _
FileFormat:= _
xlOpenXMLWorkbookMacroEnabled


And this doesn't

ThisWorkbook.SaveAs Filename:=filesavename

The workbook can be saved, but it can never be opened again.

Why?

Barb Reinhardt

 

[Bernard Liengme]

Because it has a macro! That is why MS gave us the XLSM extension, so we
know the file has a macro and we can proceed accordingly.

Try doing a manual save (Office | Save) with a macro containing file. See
what happens when you try to save as XLSX
best wishes

 

[Barb Reinhardt]

Hi Bernard,

I would have thought that if I was saving it as a .xlsm it would know that I
wanted to save it as a macro enabled workbook. I need to tell it the file
name (with the extension) and also make sure it includes the correct
FileFormat. I'd call that overkill, but then I'm not Microsoft. Should I
have been including theFileFormat all along for .xls workbooks?

Also, I'm running this code in both 2003 and 2007. I changed
xlOpenXMLWorkbookMacroEnabled to 52 (I think) and it worked in 2003 and 2007.
Is there any reason I shouldn't do this?

Thanks,
Barb Reinhardt

 

[Bernard Liengme]

I will let someone else comment on xlOpenXMLWorkbookMacroEnabled set to 52
since I have never done this

I would trap the Excel version and issue the appropriate Save command
best wishes

 

[Barb Reinhardt]

I tried that and in Excel 2003, it didn't like the
xlOpenXLMWOrkbookMacroEnabled, but it did like having it set to 52 (or
whatever it was). Thanks for your assistance

Barb Reinhardt

 

[Ron de Bruin]

Hi Barb

See
http://www.rondebruin.nl/saveas.htm

Use the number and you have no problem if you compile your project in whatever Excel version

 

[Barb Reinhardt]

Thanks Ron. That did it!

Barb

Hi Barb

See
http://www.rondebruin.nl/saveas.htm

Use the number and you have no problem if you compile your project in whatever Excel version