how can we embed the code to the excel workbook from addin

[vicky]

i have created a addin through which i can set the password and does
couple of other things as well . my need is i need to embed that code
on the report on which i use this addin . say there is a report
callled test.xls when i use my addin on this report and send it
accross the client i need my code to get copied in the report
test.xls .... since i am using this addin on couple of reports i
cannot manually copy paste the code ...

 

[Phil Hibbs]

You could write macro code that exports a module from the AddIn as a
text file, and then imports that module into the workbook. I've done
the last bit, but not the first, but I guess it should be possible.
Here's some code that imports a module into a workbook and renames it:

Dim oBook As Workbook
Dim oComponents As Object

Set oBook = ActiveWorkbook
Set oComponents = oBook.VBProject.VBComponents

oComponents.Import ("C:\VBA\MyModule.txt")
oComponents(oComponents.Count).Name = "MyModule"

I'm not sure if the VBProject stuff needs a Reference adding.

I can't actually find out how to export a module to a text file, so
you might have to do this a different way. Take a look at this:
http://vbadud.blogspot.com/2007/05/insert-procedure-to-module-using.html

That shows how to dynamically add a module and add a macro function to
it, you might be able to use a similar technique to read the contents
of the macro module in your AddIn and copy the code that way.

Phil Hibbs.

 

[Phil Hibbs]

Another method is to write a macro that saves the VBA code to a BAS
file and then read the BAS file into the 2nd workbook. Chip Pearson has
good instruction on doing this at his website

'Programming In The VBA Editor'
(http://www.cpearson.com/excel/vbe.aspx)

Best web page ever! Thanks.

Phil Hibbs.

 

[Chip Pearson]

Thanks.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Phil Hibbs]

Phil: Did you see the rest of Chip's website
'CPearson.com Topic Index' (http://www.cpearson.com/excel/topic.aspx)

OK, that's this lifetime filled up with things to read.

Phil.