[AccessD] Adding an Excel Button and Associated Macro from Access

If I have the right read on this -- note, I have never really tried -- it requires a combination of a couple things.

1) You need to create the commandbutton, as you say you have.

2) You need to use the OnAction property of the Command button to specify a macro procedure's name which is either
      a) Declared Privately on the sheet the button is on
      b) Declared Publicly in a standard module, which you will have to create or import to that workbook

3) I think you can import a module to the workbook using VBA code without requiring a reference to Microsoft Visual Basic for Applications Extensibility. But maybe not.

This assumes too that you already have a code module waiting around to be imported not. If not, it shouldn't be too hard to write one using Textstream, and then rename the file BlahBlah.Bas instead of a typical text file.

As for adding the code as a private module, this is simple with VBA Extensibility. Chip Pearson's site has these instructions.

http://www.cpearson.com/excel/vbe.aspx


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 'Steve Goodhall'
Sent: Monday, August 01, 2011 8:56 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Adding an Excel Button and Associated Macro from Access

I think it's possible, but it would be easier if you created an Excel
template that already has the button and the code and then use the
template in your "workbooks.add" statement.  Another way would be to
create a "model" notebook, open it with "workbooks.open" using "read
only" option and then save it with a different name.  Please let me
know if  you need the actual code and I will try to dig it up
tonight. 

	Regards, 
Steve Goodhall, MSCS, PMP 
248-505-5204 

----- Original Message -----
From: Access Developers discussion and problem solving 
To:"Access Developers discussion and problem solving" 
Cc:
Sent:Mon, 1 Aug 2011 07:35:45 -0500
Subject:[AccessD] Adding an Excel Button and Associated Macro from
Access

 I am trying to create a new Excel Workbook from Access. The
Worksheet
 in this Workbook needs to have a button with an associated Macro.

 So far I have been able to create the new Workbook and Worksheet and
add
 the button to the Worksheet. This works nicely.

 However, I can't figure out how to add the Excel Macro from Access.

 Is this possible?

 If so, an example would be most appreciated.

 Thanks,

 Brad

 -- 
 AccessD mailing list
AccessD at databaseadvisorscom [1]
http://databaseadvisors.com/mailman/listinfo/accessd [2]
 Website: http://www.databaseadvisors.com [3]


Links:
------
[1] mailto:AccessD at databaseadvisors.com
[2] http://databaseadvisors.com/mailman/listinfo/accessd
[3] http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com