• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3539
  • Last Modified:

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.
0
alanjbrown
Asked:
alanjbrown
  • 9
  • 6
2 Solutions
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now