Exported Query Including Macro

[bvbull200]

I have a query in Access with a saved sequence for exporting to Excel. I want the Excel file to include a macro that I created saved to it. I'm going to be updating the file once a week or so.

The file will be accessed by a large number of people, most of which have a very basic knowledge of Excel, so saving the macro to everyone's personal workbook isn't really feasible.

Is it possible for the file outputted from Access to include a macro?

 

[ranman256]

No, but you can run the Excel macro inside Access.
the Access macro would be:
transferspreadsheet ....
RunCode FormatXL()

The code ,FormatXL ,would run the exact formatting in excel ,from Access.

 

[Norie]

Why not create an Excel template workbook with the code in it and export from Access to that template/workbook?

 

[bvbull200]

No, but you can run the Excel macro inside Access.
the Access macro would be:
transferspreadsheet ....
RunCode FormatXL()

The code ,FormatXL ,would run the exact formatting in excel ,from Access.

I'll look more in to this. I'm absolutely terrible with Access, so it'll take a little trial and error, but if it can be exported with the formatting already done, that's a good thing.

Why not create an Excel template workbook with the code in it and export from Access to that template/workbook?

This is a good idea, too. Whenever it exports to the workbook, I need to make sure that it overwrites everything that was already on it (except the macro, of course). I'll look further in to this.

 

[Norie]

The template workbook should be totally blank, apart from the code of course and perhaps any 'pre-formatting' you might want.

 

[HiTechCoach]

As ranman256 pointed out, the "transferspreadsheet" creates a new file each time.

I agree with Norie that using a true Excel template is the way to go.

This may help:[h=1]Save a workbook or worksheet as a template[/h]

In Microsoft Office Excel 2007 [and later], a template file (.xltx) can include data and formatting, and a macro-enabled template file (.xltm) can also include macros.

It will require some VBA code to use Office Automation to do what you want.

 

[bvbull200]

As ranman256 pointed out, the "transferspreadsheet" creates a new file each time.

I agree with Norie that using a true Excel template is the way to go.

This may help:Save a workbook or worksheet as a template





It will require some VBA code to use Office Automation to do what you want.

The macro includes adding slicers. I feel like this changes things a little bit since it isn't strictly formatting that is being done.

I wonder what this will look like if the macro is run within Excel.