How to create a VBA Module from Delphi Code?

[deyken]

Good Day VBA Experts!

I have a "humdinger" for you today: I created a elaborate (recorded) VBA Macro to pre-format a new Excel Worksheet. What I want to do is call this macro from my Delphi 2010 Application and run this marco, so that when the ExcelApplication Interface displays to the User, the newly created worksheet has now been formatted.

I know how to call a Excel VBA Macro from Delphi, example below:

var
LCID : Integer;
XLFile : TFileName;
begin
LCID := GetUserDefaultLCID;
XLFile := 'BOM.xls';

with ExcelApplication1 do
begin
connect;
try
visible[LCID] := true;
Workbooks.Add(EmptyParam,LCID);
Run(XLFile + '!CreateExcelBOM1()');
finally
Disconnect;
end;
end;
end;

The VBA Macro (severely truncated) looks like this:

Sub CreateExcelBOM1()
'
' This Macro will attempt to re-create a simpler version of the Engineering ' Kit-up/ Bill of Materials Header and Detail sections, into which the Bill of Materials System (Engineering)
' will attempt to import BOM Data. This Version = 1.0 (21/02/2011)
'
' Keyboard Shortcut: Ctrl+Shift+B
'
'
'
ActiveWindow.SmallScroll Down:=-99
Windows("Book1").Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Bill of Materials"
Range("A14").Select
Windows("MAIN BOM TEMPLATE Vs. 1.0.xls").Activate
' And so on and so on...
End Sub

Now consider this: When I create this new workbook/worksheet via Delphi, effectively Excel opens up with an empty workbook/worksheet. There is NO VBA code existing in any of its Modules (yet).

My question: If I saved the previously written macro code into a Text File, can I "inject" this code into an empty Workbook Module? If so, how would I go about it?

I would have imagined that Delphi could expose workbook Modules (which it seemingly does via a XLApplication.Modules()) function. Also, as such, I should have been able to simply write ASCII to this Module in a Text (or similar) type of property...

Any ideas?

Regards from South Africa!
Deyken

[IBihy]

Hello Deyken,

Yes, you can read a text file containing code and use it as workbook macro. Before I get into this over my ellbows, which version of Excel are you using?

I'm currently not sure if I'm one of the last hillbillies, because I still use Excel2003.

Regards, Isabella

[IBihy]

Hi Deyken,

for Excel2003, you'd first need to tell Excel that code manipulating VBProject is trusted stuff. When you add code by automation, you are doing exactly that. Where? Workbook view, not VBE (code window).
1. Tell Excel to trust VBProject manipulation:
Tools ---> Options ----> Security ----> Macro Security ----> Trusted Publishers ----> Check box "Trust Access to Visual Basic Project".
2. in VBE view(code window), add the VB-Extensions library:
Tools ----> References ----> Check "Microsoft Visual Basic for Applications Extensibility...".
3. Save your workbook.

Now you can think of programming.
I'm just giving you the stuff you need in order to get coding from a file.


Dim VBProj As Object
Dim VBEPart as Object
Dim aCodeMod as CodeModule
' more dims, as needed

' initialize
Set VBProj = ActiveWorkbook.VBProject
Set VBEPart = Application.VBE.ActiveVBProject.VBComponents
Set aCodeMod = VBEPart.CodeModule
' Now fetch the desired stuff from a file
' If you have a package of code, not sub by sub, but the complete module
' Would have been nifty to give it a name, haven't found that, though.
aCodeMod.AddFromFile(<fully qualified file name as string>)
...
' more of your coding


HTH,
Isabella

[deyken]

Hi Isabella!

First of all, THANK YOU VERY MUCH. I was actually expecting "NO" to be the answer to my question. Actually quite glad that this is possible!

Your code looks great - just 1 question: against which event (system or otherwise) could I have this "Initialization" script fire? Essentially it would make sense to trigger it against, say: Workbook/Application start-up?

Looking forward to your reply!