modules disappear when WB copied and emailed

[keithmct]

I'm copying a master WB, renaming it with the contents of a cell (a number), save it and then I manually open outlook and send the saved WB to my boss. My WB has macros and modules and VBA buttons etc.

When he gets it, my forms & modules have gone and pressing some of my buttons don't work - create a PDF for example which was controlled by one of the modules.

I'm using Excel 2007 in compatability mode (2003)

save/rename method FileFormat:=xlNormal

I want the new workbook to be exactly the same as the master so that when he receives it via email he can do the same functions as me.

Is there an easy answer?

 

[Ruddles]

I don't know what xlNormal is. Have you tried xlExcel8?

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

 

[keithmct]

OK I tried FileFormat:=xlExcel8 and FileFormat:=52; neither work

 

[Ruddles]

You might have to post your code. If it's too long, remove any code which isn't relevant, make sure the problem still persists with the stripped-down version, and post it here.

Please post between CODE tages - the # icon in the advanced editor toolbar.

 

[Norie]

Keith

Have you checked the security settings for email/attachment/general for yourself and your boss?

It might be that somewhere along the line the code is getting stripped or disabled but you can still open the file.

Also, how is the file being opened at the other end?

 

[keithmct]

Keith

Have you checked the security settings for email/attachment/general for yourself and your boss?

I've looked into that, but I can make minor adjustments to the blank master WB and email that to him for his use and he has no problems - all the modules and macros are there, so that can't be the answer.

It might be that somewhere along the line the code is getting stripped or disabled but you can still open the file.

Also, how is the file being opened at the other end?

He saves the file I send him and then opens it from there.

I've also tried saving the master WB as macro enabled 2007 WB and emailing it to myself and it still doesn't have modules, so that's not the problem either.

Just investigated my saved files and even before they have been emailed, there are no modules, so it must be in the coding:

Private Sub export_Click()
     Worksheets(Array("Home", "Order Form", "Print Quote page", "RepairInvoice", "Prices", _
"louversonly", "Louvers Order Form", "categories")).Copy
Sheets("Home").Activate
ChDir "C:\blinds\archive\"
    ActiveWorkbook.SaveAs FileName:=(Sheets("Home").Range("S5").Value), FileFormat:=xlNormal, _
      Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False, _
      ConflictResolution:=xlLocalSessionChanges, AddToMru:=True
    ActiveWindow.Close
    ThisWorkbook.Activate
    
Sheets("Home").Activate
ActiveSheet.Select
Range("B11").Select

End Sub

 

[xenou]

Modules aren't copied with the sheets - perhaps copy the whole workbook, and then delete from it the sheets that aren't in your list.

 

[keithmct]

I don't mind copying the whole workbook each time. What do I substitute in the code?

 

[xenou]

In that case you could remove the first line:

     Worksheets(Array("Home", "Order Form", "Print Quote page", "RepairInvoice", "Prices", _
"louversonly", "Louvers Order Form", "categories")).Copy

Then perhaps try this:

ChDir "C:\blinds\archive\"
    ActiveWorkbook.[COLOR="Red"]SaveCopyAs[/COLOR] FileName:=(Sheets("Home").Range("S5").Value), FileFormat:=xlNormal, _
      Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False, _
      ConflictResolution:=xlLocalSessionChanges, AddToMru:=True

That should create a copy where you want it (test it first, as I rarely use SaveCopyAs myself and haven't tested this - any problems post back as you've got lots of good help in this thread).

 

[keithmct]

I changed coding as suggested. I had to delete all the other optional stuff as it didn't like them. So I now have that line as

ActiveWorkbook.SaveCopyAs FileName:=(Sheets("Home").Range("S5").Value) & ".xls"

This works, however, when I go to open the new workbook that I've just saved, I get a VBA error: "Compile error: Method or data member not found"
which relates to a Sub workbook_open() [which is yellow] I have in This Workbook and specifically to Calendar1.Value=Date where Calendar1 is blue.

If I go OK and close the debugger, it opens the file and everything is OK, but why does it appear?