Using Delphi WITH and Excel Automation

[6volt]

Frankly, I don't think this can be done. First, I've looked a lot simply for an example and have not found one.

In fact, I've seen this trick:


var
Sheets: Variant;
begin
XLApp.Workbooks[1].Sheets.Add(,,1,xlChart);
Sheets := XLApp.Sheets;
Sheets.Item['Chart1'].SeriesCollection.Item[1].Values := ARange;
Sheets.Item['Chart1'].ChartType := xl3DPie;

One would think that

With XLap DO BEGIN
Sheets.Item (...)
...
END

Would work, but it will not compile - as if it doesn't recognize the XL object. I suppose that maybe is the point, Delphi simply does not know what XL is so when you try to use WITH, Delphi simply does not know WHAT to prefix with XLap.

Or, is there a way to use WITH?

Thanks
Tom

 

[majlumbo]

Here's a good example of how to automate excel from Delphi. Which is entirely possible...

http://support.microsoft.com/kb/231614

 

[6volt]

Thanks for the MS url.

That one seems familiar - I probably came across it in my vast internet search on the topic.

While a rather complicated example, there are 15 lines of code that begin with

oResizeRange.

which is just where you would want to use the WITH structure.

I suspect it does not work in Delphi since Delphi does not know the hierarchy of Excel.

But I did successfully use the "Sheets" method I briefly started out describing in this thread. While that example is quite brief, the assignment can be very long and can eliminate a great amount of "pre" redundancy. In fact, the structure is just like WITH except that a Variant assignment is used.

Something like

With ActiveChart
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"

in VBA becomes

VAR
XL : Variant
BEGIN
XL := XLap.ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters;
XL.Text := "Month";
XL.font.bold := True;

 

[DjangMan]

I've done lots of work with automating Excel with Delphi and the intellisense/codecomplete can't offer you suggestions when it doesn't know what the type of variable it is. So things like XLApp.ActiveSheet may not return a _Worksheet according to the IDE but it is according to your code. You might try coding like _Worksheet(XLApp.ActiveSheet) so that the IDE has some idea what you're working with.

That all being said there is a very active discussion on whether to use With or not.

 

[6volt]

Djang

I've never seen that style of code that you suggested.

The standard line of code I left out of my examples is the common:


XLAp := CreateOleObject('Excel.Application');


From what I can tell, WITH does not work, you simply use a "WITH" Variant which is assigned.

It accomplishes the same thing with the same amount of code.

My biggest problem now is PAGE.SETUP which is broken and very slow. It is compounded by the fact that you cannot setup headers for all sheets, charts, and embedded charts in a workbook. Kinda' defeats the concept of headers.

 

[DjangMan]

Here's an example of a function in my code that calls the .Find method. That method, in Excel, returns a Range and I suspect the definition in my TLB file says it returns a _Range. ExcelRange is defined somewhere in the TLB file but isn't shown as the default type being returned by Find so the code completion doesn't work with helping it. This is using early binding so I get better speed vs. late binding.

function TdmXL.FindNextXLBudFormula(aCurRow, aCurCol, aCurrSheetIndex: integer; var bNewRow, bNewCol: integer): boolean;
var
  FindRange: ExcelRange;
begin
    result := false;
    FindRange := xlsheet.Cells.Find('XLBud(', xlApp.ActiveCell, xlValues, EmptyParam, xlByRows, xlNext, False, False, EmptyParam);
    if IsNothing(FindRange) then exit;

    if xlsheet.Index[lcid]=aCurrSheetIndex then begin
        if (FindRange.Row=lastFxlRow) and (FindRange.Column=lastFxlCol) then exit;

        result := true;
        bNewRow := FindRange.Row;
        bNewCol := FindRange.Column;
        exit;
    end;
end;

 

[6volt]

Djang,

Thanks for the example code. You're comment that "code completion doesn't work" I think also implies the WITH will not work either.

But that is a minor point.

You have used something called a TDL file which is something I've never seen. I did a quick search on it and found that it is some kind of math data transfer file. For now, I'm clueless how to do this kind of programming.

Also, your function name is not simple, it implies again programming I have never seen before.

Any chance of a link or further explanation?
_________________________________-


Regarding PAGE.SETUP (XL4macro) used to speed up setting page parameters - they say it is slow because each time you set one, XL talks to the printer. I am going to try setting the printer to something that is not hardware so it will be fast, make the changes, and then restore the printer setting.

 

[DjangMan]

I'm not using a TDL. I meant to type TLB (type library). Delphi can create a wrapper around an ActiveX/COM library so you can use early binding.

My function was just to show you how you might receive the result from an Excel function and use that later and still be able to use code completion.

Here is some more code that works on a given worksheet number and locates the last row and column used in that sheet.

Search for Deborah Pate and you'll find her pages on automating Excel using early and late binding.

procedure TdmXL.ProcessSheet(const aSheetNumber: integer;
                             var MultiSheetPass: boolean;
                             var bNeedToRepeat: boolean);
var
  ixlCol, ixlRow: integer;
  xlRange       : ExcelRange;
  SafetyCnt     : integer;
  i             : integer;
  NestedFormula : boolean;

  sFormula      : string;
  xlCellValue   : OLEVariant;
  NewxlRow, NewxlCol: integer;

begin
    SafetyCnt := 0;
    XLSheet := XLBook.Worksheets[aSheetNumber] as _Worksheet;

    //set focus to cell 1,1
    FxlRow := 1;
    FxlCol := 1;
    lastFxlRow := 0;
    lastFxlCol := 0;
    ActivateCurrentCell;

    try
        {$ifdef Excel2000}
        xlLastRow := XLSheet.Cells.Find('*',EmptyParam,EmptyParam,EmptyParam,xlByRows,xlPrevious,EmptyParam,EmptyParam).Row;
        xlLastCol := XLSheet.Cells.Find('*',EmptyParam,EmptyParam,EmptyParam,xlByColumns,xlPrevious,EmptyParam,EmptyParam).Column;
        {$else}
        xlLastRow := XLSheet.Cells.Find('*',EmptyParam,EmptyParam,EmptyParam,xlByRows,xlPrevious,EmptyParam,EmptyParam,EmptyParam).Row;
        xlLastCol := XLSheet.Cells.Find('*',EmptyParam,EmptyParam,EmptyParam,xlByColumns,xlPrevious,EmptyParam,EmptyParam,EmptyParam).Column;
        {$endif}
    except
        on e:exception do begin
          ReportError(e,999); //to screen for now
          xlLastRow := 0;
          xlLastCol := 0;
        end;
    end;

 

[6volt]

Djang,

WOW!

I have to laugh - the way I was doing it was the specific way Pate said to AVOID!

This Early Binding stuff is all new to me and is going to take some time for me to figure out. If it is faster, then that is VERY important to me because I am writing this neural network backtester that uses EXCEL for all output workups and charting. The charting is particularly slow. PageSetup takes seconds to set the Header and Footer(!)

Anyhow, it sill take me some time to digest all this so don't expect any replies soon(!)

Thank you so much!
Tom

 

[DjangMan]

Early binding is faster in the sense that each call you make doesn't have to be looked up as it is with late binding. So if you're making many Excel calls then that will be slower using late binding. However, if you mostly want to push a bunch of data to Excel and then build some charts there might be some faster ways of doing this.

One way might be to push the data into Excel using variant arrays. That will be a much much faster way of pushing the data in.

Another option might be to build the Excel workbook with named ranges and make the charts reference the named ranges. Then push in the data using variant arrays. This will let you make Excel do one large calculation vs. more smaller ones.

 

[6volt]

A very interesting phenomenon using this modified "WITH" method:

One would think the following would work (at least I did), but in fact, the last statement fails:

XLap : Variant;
...
XLap := CreateOleObject('Excel.Application');
XLap.Charts.Add;

XL := XLap.ActiveChart;   {This is the clever bit!}

XL.ApplyCustomType (xlUserDefined, 'NDXseedStat180');
XL.SetSourceData(XLap.Sheets[sheetName].Range[crange],xlColumns)
XL.Location (xlLocationAsObject, sheetName);
XL.Parent.Name := cName;

It turns out what must be used to make it work is:

XLap.ActiveChart.Parent.Name := cName;

After the "hierarchy" has been "reset," it is possible to use the XL:=XLap.ActiveChart again.

I suppose the reason the first example fails is when the location of the chart is moved and the ChartObjects object is introduced, there is now a new "object path" to the Charts object. (excuse my Object-Speak)

Better Explanations are eagerly welcomed!

Thanks
Tom

 

[DjangMan]

You see when you record Excel macros that they are very explicit. It might mean more typing for you but at least you won't get caught by odd problems like you found.