Consulting

Results 1 to 4 of 4

Thread: How to create a VBA Module from Delphi Code?

  1. #1
    VBAX Regular
    Joined
    May 2009
    Location
    Johannesburg
    Posts
    69
    Location

    How to create a VBA Module from Delphi Code?

    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:

    [VBA]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[/VBA]

    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
    Deyken
    DeezineTek
    South Africa

  2. #2
    VBAX Regular
    Joined
    Feb 2011
    Posts
    75
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Feb 2011
    Posts
    75
    Location
    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.

    [VBA]
    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
    [/VBA]

    HTH,
    Isabella

  4. #4
    VBAX Regular
    Joined
    May 2009
    Location
    Johannesburg
    Posts
    69
    Location

    When would I run the Initialization Code

    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!
    Deyken
    DeezineTek
    South Africa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •