Insert module into workbook via automation?

[deko]

Is it possible to insert a VBA code module into an existing Excel workbook
from Access 2000?

I need to add a button on certain worksheets (some worksheets, not all) that
will create charts. Unfortunately, I do not own the workbook and have to
append data and charts from a separate Access database. I want to add a
button and code for creation of more charts, but don't know if this is
possible. If I could insert a VBA module into the workbook, that might do
it. If someone could point me in the right direction on this that would be
great.

Thanks in advance.

 

[Dave Peterson]

Depending on how security is set up, you may be able to do it using techniques
at Chip Pearson's site:

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

Or maybe you could just create the charts from Access--or create an addin that
has the code already in it and you could just load that addin and run that code.

 

[deko]

Depending on how security is set up, you may be able to do it using
techniques

at Chip Pearson's site:

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

I found this code on the site:

Sub AddModule()
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "NewModule"
Application.Visible = True
End Sub

Looks like what I need - I'll give it a shot.

Or maybe you could just create the charts from Access--or create an addin that
has the code already in it and you could just load that addin and run that

code.

Perhaps. I think what *should* be done is write it in C# with VSTO...

Thanks for the tip.