We help IT Professionals succeed at work.

Delphi OLE excel macros

alanjbrown
alanjbrown asked
on
4,303 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.
Comment
Watch Question

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');


Author

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.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Is the error message 'Subscript out of range'? Because that is the error message I am receiving.
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.

Author

Commented:
In that case, I had probably better have a 'module 1' in my project. How should I add the module?
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.

Author

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?
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 !

Author

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.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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

Author

Commented:
Thanks David.

Author

Commented:
wydsiwyrg, I know it can be done using the TExcelApplication, but I was wondering if it can be done using OLE.

Author

Commented:
(what you do see is what you really get?)

Author

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;

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.