Excel automation (inserting macros programmatically)


I wrote an application a while ago that creates reports in excel programatically using the Excel Object Library. At the time of development, I had Windows 2000 with Office 2000. I tested this on a machine with Windows XP and Office 2000 and it worked.

Now I have a machine with Windows XP and Office 2003. The application now generates an error: "Error 1004: Programmatic access to Visual Basic is not trusted". This, of course, happens on the line that tries to add a VBA macro as follows:

   Dim str As String
   Dim eCM As VBIDE.CodeModule
   Dim eVBC As VBIDE.VBComponent
   Set eVBC = eWB.VBProject.VBComponents.Add(vbext_ct_StdModule) ' Error here
   eVBC.Name = "mdlAudioBee"
   str = "Some Macro Text"
   eVBC.CodeModule.InsertLines 1, str

I have already set the lowest possible value for Excel > Tools > Macros > Security > Low and have also disabled my AntiVirus program (Norton).

What else do I need to do to allow the above operation?


Who is Participating?
raheelasadkhanAuthor Commented:
Worked nicely. Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.