Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delphi OLE excel macros

Posted on 2004-10-22
16
Medium Priority
?
3,396 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
Comment
Question by:alanjbrown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 6

Expert Comment

by:david_barker
ID: 12378763
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
ID: 12378881
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:
david_barker earned 1200 total points
ID: 12379058
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:alanjbrown
ID: 12381565
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
ID: 12381695
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
ID: 12381817
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
ID: 12381867
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
ID: 12381968
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
ID: 12382019
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
ID: 12382144
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
david_barker earned 1200 total points
ID: 12384176
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
ID: 12386920
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
ID: 12398220
Thanks David.
0
 
LVL 1

Author Comment

by:alanjbrown
ID: 12398359
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
ID: 12398361
(what you do see is what you really get?)
0
 
LVL 1

Author Comment

by:alanjbrown
ID: 12399227
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

609 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