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

V

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 ...
 
P

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.
 
J

joel

The easiest method is to create a template workbook with the macro i
the template. Then make a copy of the template when you start you
macro.

If code is located on the VBA sheet (not module) if you copy the shee
the code also gets copies. In VBa if you copy a sheet and do not us
Affter or Before it automatically creates a new workbook with only on
sheet.

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

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

Read the two bullets at the top of the webpage espcially the on
below.

•Next, you need to enable programmatic access to the VBA Project. I
Excel 2003 and earlier, go the Tools menu (in Excel, not in the VB
editor), choose Macros and then the Security item. In that dialog, clic
on the Trusted Publishers tab and check the Trust access to the Visua
Basic Project setting
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Embed the same Excel multiple times 0
can we copy the code from the addin to excel workbook 2
Copy a Sheet to an AddIn 2
How can I Lock a Link? 0
Restrict AddIn to limited users 3
Addin compatibility between Excel 2k3 and Excel 2k7 4
Trying to read CustomDocumentProperties of an open 2007 AddIn 1
Addin conflicting with Workbook Module 3

Top