Delphi OLE excel macros

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.
LVL 1
alanjbrownAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

david_barkerCommented:
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
alanjbrownAuthor Commented:
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
david_barkerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

alanjbrownAuthor Commented:
Is the error message 'Subscript out of range'? Because that is the error message I am receiving.
0
david_barkerCommented:
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
alanjbrownAuthor Commented:
In that case, I had probably better have a 'module 1' in my project. How should I add the module?
0
david_barkerCommented:
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
alanjbrownAuthor Commented:
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
david_barkerCommented:
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
alanjbrownAuthor Commented:
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
david_barkerCommented:
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
wydsiwyrgCommented:
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
alanjbrownAuthor Commented:
Thanks David.
0
alanjbrownAuthor Commented:
wydsiwyrg, I know it can be done using the TExcelApplication, but I was wondering if it can be done using OLE.
0
alanjbrownAuthor Commented:
(what you do see is what you really get?)
0
alanjbrownAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

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.