Board index » delphi » Running Excel VBA macro from Delphi ...

Running Excel VBA macro from Delphi ...

Hi!

I have written a VBA macro that automates some tasks in my Excel worksheet
(MyExcelProgram.xls). It runs perfectly well when initiated from within
Excel. Is it possible to execute this same macro from a Delphi application?
If it is, would it be possible to do it WITHOUT Excel being launched by
Delphi?

I know that I can write routines in Delphi that can simulate some of the
common tasks in Excel (e.g., create a new worksheet, select a range, bold
font of certain cells, etc.). However, I don't want to have to translate all
of my VBA code in Delphi "line-by-line", as I'm afraid there will be no
direct translation for some VBA calls I've used in Excel.

Thanks for your help.

Greg

 

Re:Running Excel VBA macro from Delphi ...


you have a couple of options.
 use the
MyVariant := CreateOLEObject('Excel.Document');
MyVariant.Open('filename x....');
 --
secondly if you look a the server tabs you will find one for the
Excel.
Quote
Greg_Del_Pilar wrote:
> Hi!

> I have written a VBA macro that automates some tasks in my Excel worksheet
> (MyExcelProgram.xls). It runs perfectly well when initiated from within
> Excel. Is it possible to execute this same macro from a Delphi application?
> If it is, would it be possible to do it WITHOUT Excel being launched by
> Delphi?

> I know that I can write routines in Delphi that can simulate some of the
> common tasks in Excel (e.g., create a new worksheet, select a range, bold
> font of certain cells, etc.). However, I don't want to have to translate all
> of my VBA code in Delphi "line-by-line", as I'm afraid there will be no
> direct translation for some VBA calls I've used in Excel.

> Thanks for your help.

> Greg

Re:Running Excel VBA macro from Delphi ...


Quote
"Greg_Del_Pilar" <Greg_Del_Pi...@hotmail.com> wrote in message

news:7WsDa.151664$Vi5.4307421@news1.calgary.shaw.ca...

Quote
> Hi!

> I have written a VBA macro that automates some tasks in my Excel worksheet
> (MyExcelProgram.xls). It runs perfectly well when initiated from within
> Excel. Is it possible to execute this same macro from a Delphi
application?
> If it is, would it be possible to do it WITHOUT Excel being launched by
> Delphi?

You could program in Visual Basic instead :)

Cheers,
Nicholas Sherlock

Re:Running Excel VBA macro from Delphi ...


Nicholas:

Thanks! I have definitely considered trying to use VB6 to accomplish what
I've done in Excel, but I thought I should be able to do in Delphi what I
can in VB. Also, the VBA application I wrote does a lot of Excel chart
manipulations (change series parameters, add secondary axis, change plot
area background, move bar chart labels, etc.) in addition to worksheet
manipulations. I don't have VB so I'm not sure if these Excel VBA
capabilities can be simulated in VB.

Greg

Quote
"Nicholas Sherlock" <n_sherl...@hotmail.com> wrote in message

news:bbmuhj$c2f$1@lust.ihug.co.nz...
Quote
> "Greg_Del_Pilar" <Greg_Del_Pi...@hotmail.com> wrote in message
> news:7WsDa.151664$Vi5.4307421@news1.calgary.shaw.ca...
> > Hi!

> > I have written a VBA macro that automates some tasks in my Excel
worksheet
> > (MyExcelProgram.xls). It runs perfectly well when initiated from within
> > Excel. Is it possible to execute this same macro from a Delphi
> application?
> > If it is, would it be possible to do it WITHOUT Excel being launched by
> > Delphi?

> You could program in Visual Basic instead :)

> Cheers,
> Nicholas Sherlock

Re:Running Excel VBA macro from Delphi ...


// static
    FvExcel    : OLEVariant;
// can be temporary
  vWorkbook  : OLEVariant;

// Kill existing instance if not first time & user hasn't killed it (you may
want something more appropriate here)
  if not VarIsEmpty ( FvExcel ) then
  begin
    try
      FvExcel.Quit;
    except
    end;
  end;
// Start it
  FvExcel := CreateOleObject('Excel.Application');
  FvExcel.Visible := true;

 // Run the code that is in the macro "DoSomething", which is held in the
..\XL\MyMacro.xls
// Mine takes one parameter (more can be added)

  vWorkbook := FvExcel.Workbooks.Open( ExtractFilePath(Application.ExeName)
+ '..\XL\MyMacro.xls');
  FvExcel.Application.Run('DoSomething',  'FirstParameter');
  vWorkbook.Close;

It took longer than I care to remember to get this sorted out ...
but i prefered to keep the worker code in Excel rather than do it all from
Delphi

Regards
David Pierson
Brisbane

Quote
"Greg_Del_Pilar" <Greg_Del_Pi...@hotmail.com> wrote in message

news:2iKDa.158071$Vi5.4465306@news1.calgary.shaw.ca...
Quote
> Nicholas:

> Thanks! I have definitely considered trying to use VB6 to accomplish what
> I've done in Excel, but I thought I should be able to do in Delphi what I
> can in VB. Also, the VBA application I wrote does a lot of Excel chart
> manipulations (change series parameters, add secondary axis, change plot
> area background, move bar chart labels, etc.) in addition to worksheet
> manipulations. I don't have VB so I'm not sure if these Excel VBA
> capabilities can be simulated in VB.

> Greg

Re:Running Excel VBA macro from Delphi ...


David:

THANK YOU, THANK YOU, THANK YOU!
This is exactly what I'm asking about. I wanted to keep all the program
logic in Excel VBA and execute it from Delphi, rather than convert
everything in Delphi.

Greg

Quote
"David Pierson" <eshar...@yahoo.com> wrote in message

news:lc%Da.50567$1s1.673297@newsfeeds.bigpond.com...
Quote
> // static
>     FvExcel    : OLEVariant;
> // can be temporary
>   vWorkbook  : OLEVariant;

> // Kill existing instance if not first time & user hasn't killed it (you
may
> want something more appropriate here)
>   if not VarIsEmpty ( FvExcel ) then
>   begin
>     try
>       FvExcel.Quit;
>     except
>     end;
>   end;
> // Start it
>   FvExcel := CreateOleObject('Excel.Application');
>   FvExcel.Visible := true;

>  // Run the code that is in the macro "DoSomething", which is held in the
> ..\XL\MyMacro.xls
> // Mine takes one parameter (more can be added)

>   vWorkbook := FvExcel.Workbooks.Open(

ExtractFilePath(Application.ExeName)

- Show quoted text -

Quote
> + '..\XL\MyMacro.xls');
>   FvExcel.Application.Run('DoSomething',  'FirstParameter');
>   vWorkbook.Close;

> It took longer than I care to remember to get this sorted out ...
> but i prefered to keep the worker code in Excel rather than do it all from
> Delphi

> Regards
> David Pierson
> Brisbane

> "Greg_Del_Pilar" <Greg_Del_Pi...@hotmail.com> wrote in message
> news:2iKDa.158071$Vi5.4465306@news1.calgary.shaw.ca...
> > Nicholas:

> > Thanks! I have definitely considered trying to use VB6 to accomplish
what
> > I've done in Excel, but I thought I should be able to do in Delphi what
I
> > can in VB. Also, the VBA application I wrote does a lot of Excel chart
> > manipulations (change series parameters, add secondary axis, change plot
> > area background, move bar chart labels, etc.) in addition to worksheet
> > manipulations. I don't have VB so I'm not sure if these Excel VBA
> > capabilities can be simulated in VB.

> > Greg

Other Threads