Solved

Delphi OLE excel macros

Posted on 2004-10-22
3,018 Views
Last Modified: 2011-10-03
Running Excel 2000 through OLE in Delphi 7, I was wondering if there is any way to create and run an excel macro.


Any ideas would be appreciated.
0
Question by:alanjbrown
    16 Comments
     
    LVL 6

    Expert Comment

    by:david_barker
    For running an Excel Macro from Delphi :
      FExcel: Variant;
      FExcel:=CreateOleObject('Excel.Application');
      FExcel.Workbooks.Open('MYXLS.XLS',EmptyParam,EmptyParam);   ' which includes a public sub (a macro)
      FExcel.Run('MYMACRONAME');


    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    David, I know that works, but I was wondering if there's any way to create a macro in the first place through the OLE in Delphi.
    0
     
    LVL 6

    Accepted Solution

    by:
    Something like this should not be too far, but I get an error message I imagine is related to my security options :

    procedure TForm1.Button1Click(Sender: TObject);
    const
      vbext_ct_StdModule = 1;
    var
      objModule : OleVariant;
    begin
      ExcelApplication1.Workbooks.Open('C:\Temp\ajeter.xls',EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
      objModule:=ExcelApplication1.VBE.ActiveVBProject.VBComponents.Item('Module1');
      objModule.CodeModule.InsertLines(1,'Public sub HiThere()');
      objModule.CodeModule.InsertLines(2,'ShowMsg("Hi there !")');
      objModule.CodeModule.InsertLines(3,'End Sub');
      ExcelApplication1.ActiveWorkBook.Save(0);
    end;
    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    Is the error message 'Subscript out of range'? Because that is the error message I am receiving.
    0
     
    LVL 6

    Expert Comment

    by:david_barker
    No, I get an error related to security. I'm not posting it, because it is in French (that the language of my computer !).
    On which line do you get the error ?
    My code assumes that you already have a module named "Module1" in the project.
    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    In that case, I had probably better have a 'module 1' in my project. How should I add the module?
    0
     
    LVL 6

    Expert Comment

    by:david_barker
    In your Excel file (in my case 'C:\Temp\ajeter.xls'), do <Alt><F11> and then right-clic on the left pane and insert a Module.
    Write a little sub like
      Public sub Test
        ShowMsg("Test")
      end sub
    and save the Excel file.
    Quit Excel and try the Delphi code.
    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    Is there anyway to create a module from scratch? I was hoping to be able to open a new excel sheet, in a  new workbook,
    and then add a couple of macros. Is that possible?
    0
     
    LVL 6

    Expert Comment

    by:david_barker
    That seems far more complicated.
    Perhaps another expert, more specialised in the object model of the VBIDE might have an idea, but this doesn't seem to be a common thing for people to want to do !
    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    Okay, thanks for the help. I'll award the 200 points, but first, for another 100 points...

    The reason I started thinking about running macros in excel was to be able to set the default save directory of a single
    excel workbook. This is something I've done easily enough using the Word2000 server components, but there doesn't appear
    to be any such functionality for excel. Macro-wise, the macro is "ChDir('Filepath')", which doesn't do anything from delphi
    automation. Any ideas would be appreciated.

    Thanks in advance.
    0
     
    LVL 6

    Assisted Solution

    by:david_barker
    Ok, ok, here we go (what would one do for a few points !).
    Some of my wording concerning the menus might be incorrect (but not the code which will be correct), because I'm using a French version of Excel.
    But I'm sure we will figure it quickly.

    In Excel, create an empty workbook.
    In VBA, create a class module, and name it "ExcelApplication".
    Paste the following in this class module :

      Public WithEvents AppXl As Application
      Private Sub AppXl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
        ChDir ("c:\temp")  ' or whatever you fancy
      End Sub

    In the code for "ThisWorbook", paste the following :
     
      Dim HookXL As New ExcelApplication
      Private Sub Workbook_Open()
        Set HookXL.AppXl = Application
      End Sub

    Save the worbook as a .XLA add-in (last file format choice in my case), ie AutoChangeDir.xla
    Go to menu Tools -> Add-in macros (or something like that in English) and select AutoChangeDir.xla
    Close Excel, restart it, and the next time you save any workbook, it should show you the directory selected previously.

    I really hope it works the same for you !

    David
    0
     

    Expert Comment

    by:wydsiwyrg
    Hello mate,
    this is how I do it:

    first on Excel go to Tools -> Macro -> Security -> Trusted Publishers and check Trust Access to Visual Basic Project
    then try the following code:

    Insert a TExcelApplication component on your form and a buttom

    procedure TForm1.Button1Click(Sender: TObject);
    var
      Module: Variant;
    begin
      ExcelApplication1.Visible[0] := True;
    ExcelApplication1.Workbooks.Open('C:\temp\temp.xls',EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
    {create the module}
      Module := ExcelApplication1.VBE.ActiveVBProject.VBComponents.Add(1);
      Module.CodeModule.InsertLines(1,'Public sub test()');
      Module.CodeModule.InsertLines(2,'ShowMsg("helloworld")');
      Module.CodeModule.InsertLines(3,'End Sub');
      ExcelApplication1.ActiveWorkbook.Save(0);
    end;

    hope this helps,
    Cheers mate,
    Tim
    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    Thanks David.
    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    wydsiwyrg, I know it can be done using the TExcelApplication, but I was wondering if it can be done using OLE.
    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    (what you do see is what you really get?)
    0
     
    LVL 1

    Author Comment

    by:alanjbrown
    OKay, I did it. wydsiwyrg, sprry for the lack of points here. Between you and David, I managed to set up a bit of code to create a module, create a macro and run it. Thanks to both of you.

    procedure TForm1.setmacro;
    var
      objModule : OleVariant;
    begin
      objModule := ExcelApplication1.VBE.ActiveVBProject.VBComponents.Add(1);
      objModule.CodeModule.InsertLines(1,'Public sub HiThere()');
      objModule.CodeModule.InsertLines(2,'ShowMsg("Hi there !")');
      objModule.CodeModule.InsertLines(3,'End Sub');
      XLApplication.Run('HiThere()')
    end;
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
    Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now