Access VBA Creates an Excel with built-in Macro

[vgarzon]

Hi all,

I've recently developed an Access 2007 application, that creates a report in an Excel Sheet.

Now I need to put a button in that sheet to switch from one view with certain columns, to another one with other visible columns.

I have the view switching code writen in excel, and I can create the button, but I don't know how to:

1. Put the view switching code in the generated excel file.
2. Associate that code to the OnClick action of the created button.

Can anyone help me please? Thanks in advance

Vic

 

[DCrake]

I think that the easiest way to do this is to first create an Excel template with the button and associated macro in it. Then from Access make a copy of this template and populate the new workbook. Now when the user opens the workbook the embedded macro will be there.

This is the simplified version. Alot will depend of the complexity of the macro.

David

 

[vgarzon]

Thanks DCrake, I think that is the shortest and easiest solution. Sadly I can't do it because the process can't change that much; I can only modify the excel creation macro to adapt it to the way I want. Thanks a lot anyways.

Does anybody knows another way of doing it?

 

[vgarzon]

Done,

The first thing is to write the macro as if it were writen for an Excel VBA module, and save it as a *.bas file

This should go on the excel creation file

'Add the view button
        appExcel.ActiveSheet.Buttons.Add(520, 110, 130, 25).Select
        appExcel.ActiveSheet.Shapes("Button 1").Select
        appExcel.Selection.Characters.Text = "Processing View"
        appExcel.VBE.ActiveVBProject.VBComponents.Import "C:\Program Files\BlaBlaBLa\Change_Views_Script.bas"
        appExcel.ActiveSheet.Shapes("Button 1").Select
        appExcel.Selection.OnAction = "Change_Views"

"Change_Views_Script.bas" is the file that contains the function "Change_Views". It is simply imported and called. You would have to check the option "Trust access to the VBA project object model" on excel, in the trust Center settings (2007). To do that you can also set to 1 the register in HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\AccessVBOM

I hope it helps someone.