SaveAs Macro Enabled Workbook Excel 2010

[Esham]

We've been using the same macro for years on the older Office versions. Now we have Office 2010 and the macro still works except for saving the workbooks. I've attached screen shots of the errors. Here is a quick breakdown of what the macro does:

1. Opens a template file (which already has macros inside it).
2. Opens some other excel files and manuiplates and transfers the data to the template file.
3. Saves the template file as a new workbook with a unique name.

The problem with the new Office 2010 is that I cannot figure out how to write the code to save as a macro enabled file?? Here is the code snipit where I am trying to save the file and where I get the errors. The error comes after the "MasterWb.SaveAs...." line.

Any help would be greatly appreciated!!

Workbooks.Open(Filename:="H:\Maintenance\Schedule Template\Rochelle Weekly Schedule Individual Master.xltm").RunAutoMacros Which:=xlAutoOpen
DO ALL THE DATA MANIPULATION STUFF
fileSavePath = DiscWB.Path & "\"
fileSaveName = "Contract " & Format(Now + 3, "mm-dd-yyyy") & ".xlsm"
MasterWb.SaveAs Filename:=fileSavePath & fileSaveName

[Siddharth Rout]

Try this

MasterWb.SaveAs Filename:=fileSavePath & fileSaveName, Fileformat:=xlOpenXMLWorkbookMacroEnabled

or

MasterWb.SaveAs Filename:=fileSavePath & fileSaveName, Fileformat:=52

[Esham]

Try this

MasterWb.SaveAs Filename:=fileSavePath & fileSaveName, Fileformat:=xlOpenXMLWorkbookMacroEnabled

or

MasterWb.SaveAs Filename:=fileSavePath & fileSaveName, Fileformat:=52

Okay, these seemed to do the trick but now I get this error. Any ideas on how to fix?

[Siddharth Rout]

Are you trying to protect the workbook using a password?

[Esham]

Are you trying to protect the workbook using a password?

Yes, I am trying to protect using a password.

[Siddharth Rout]

Can you share the code that you are using?

[Esham]

Do you want the entire macro code, or just the relevant parts?

[Siddharth Rout]

Just the part where you are protecting. I believe that you are protecting the workbook with the option "Protect Structures and Windows" set to true. Just protect the workbook without using "Protect Structures and Windows" and you will be just fine...

[Esham]

Just the part where you are protecting. I believe that you are protecting the workbook with the option "Protect Structures and Windows" set to true. Just protect the workbook without using "Protect Structures and Windows" and you will be just fine...

Unfortunately there are areas of the book that they need to be able to modify but many other areas that need to be protected. Here is the code.

WsWeekly.Protect ("HorMel2009"), DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True
MasterWb.UpdateLinks = xlUpdateLinksAlways
MasterWb.WritePassword = "book"
MasterWb.SaveAs Filename:=fileSavePath & fileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled 
Set TrayWb = Workbooks(fileSaveName)

[Siddharth Rout]

Try this for me...

fileSaveName = "Contract " & Format(Now + 3, "mm-dd-yyyy") & ".xls"
MasterWb.SaveAs Filename:=fileSavePath & fileSaveName, FileFormat:=xlExcel8

[Esham]

Try this for me...

fileSaveName = "Contract " & Format(Now + 3, "mm-dd-yyyy") & ".xls"
MasterWb.SaveAs Filename:=fileSavePath & fileSaveName, FileFormat:=xlExcel8

This didn't work. It allowed me to save the file but with a notification about updating the cells when the book is reopened. Here is what is interesting. When I open the template the first time (in the code) it works just fine. After I save that file (with the code we are talking about) and I open the template again (looping in the code); is when I get the error about the macros in the workbook???

[Esham]

It doesn't happen the first time the template is opened, just on the second one.

Workbooks.Open(Filename:="H:\Maintenance\Schedule Template\Rochelle Weekly Schedule Individual Master.xltm").RunAutoMacros Which:=xlAutoOpen

DO SOME MACRO STUFF

MasterWb.SaveAs Filename:=fileSavePath & fileSaveName, FileFormat:=xlExcel8
    Set CONWB = Workbooks(fileSaveName)

Workbooks.Open(Filename:="H:\Maintenance\Schedule Template\Rochelle Weekly Schedule Individual Master.xltm").RunAutoMacros Which:=xlAutoOpen

DO SOME MACRO STUFF

MasterWb.SaveAs Filename:=fileSavePath & fileSaveName, FileFormat:=xlExcel8
    Set ENGWB = Workbooks(fileSaveName)

[Siddharth Rout]

I think I will have to see your complete code. Let me have a look at it from a fresh pair of eyes when I wake up in the morning. It is 2:30AM here and I was about to go to bed.

If you can show the complete code and also if possible can you zip the template and upload it? I will play with it in the morning.