Include excel macro when creating spreadsheet

[presuming_ed]

I have an Access application that creates excel spreadsheets and emails them to a number of users.

I have a requirement to include an excel macro with the spreadsheet when it is sent to the users, so that when they open the spreadsheet in excel the macro runs and reformats the spreadsheet.

The contents of the spreadsheet will change frequently so I don't want to rely on having copies of the macro on each individual PC, but would rather distribute a new macro each time I mail the spreadsheet.

Has anyone done anything similar, or have any ideas on how I can do this.

Thanks.

 

[Mile-O]

One way I can think of - although I've never done it - would be to copy your macro into an array and then rewrite it in the creation of the spreadsheet.

The basic principle would be to count the number of lines in the relevant module.

Dimension an array with an unspecified amount

and then store each line of the module into the array using the ReDim Preserve statements.

Now that you have the entire module in an array variable you can then write it into your spreadsheet.

 

[Mile-O]

This thread may be able to help you understand the method of using lines in modules.

 

[presuming_ed]

Thanks Mike.... sounds great, but just one question. Any ideas on how I would write this into my spreadsheet, as a present I'm just creating my spreadsheet using:

DoCmd.OutputTo acOutputTable, "email_this", acFormatXLS, "C:\filename.xls", False

The table "email_this" just contains rows of data, so I'm not sure how I embed the macro. Do I need to alter my method of creating the spreadsheet?

 

[Mile-O]

First of all - why does everyone call me Mike?

I've never bothered to link into Excel before but I suppose once you've created your spreadsheet you can tap into the Excel Object Model and create a module and then put the code into that module.

You'd need to set the references to include the Excel object library.

 

[presuming_ed]

Thanks... I'll give it a whirl. Sorry about the dyslexia!