Excel automation (inserting macros programmatically)
Posted on 2006-04-12
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?