Controlling Excel 2010 with Delphi 2010 using OfficePartner or COM Automation

[agrarian]

I have a project where I need to retrieve data from MS SQL Server and format it into an Excel Spreadsheet. I am using the OfficePartner component to try and make this easier, but I'm having a hard time figuring out how to get the VB help and Excel Object Model and Excel Macro code all to help me to figure out how to write my Delphi code.

This is my current code:

Rng := OpExcel.Workbooks[0].Worksheets[0].Ranges.Add;
    Wkst := OpExcel.Workbooks[0].Worksheets[0];
    Rng.Name := 'UpdateRange';
    Rng.Address := 'A1';
    Rng.OfficeModel := DataModule1.mdlUpdates;
    DataModule1.mdlUpdates.Dataset :=
      DataModule1.spGetLabelUpdates;
    OpExcel.RangeByName['UpdateRange'].Populate;
    Wkst.Activate;
    Rng.Address := 'A1:M59';
    Rng.Select;
    Rng.AutoFitColumns;
    Wkst.Name := 'Testing';

So far, all this works. However, there is one problem: normally I will not know the number of rows inserted into the worksheet, so I need to somehow set the Rng.Address to include ALL data (rows and columns).

To see if I could figure it out, I recorded some macros in Excel while I accomplished the missing tasks. But I can't figure out how to use the VB Script in my Delphi app.

VB Macro: Sub ExcelMacro()
'
' ExcelMacro Macro
'
'
' 1) Select All Cells and then select Format as Table
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Name = "AllSelect"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("AllSelect"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium9"

' 2) Save File to network
ActiveWorkbook.SaveAs Filename:= _
"\\Server\Path\Filename.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

' 3) Select File / Save & Send / Send Using E-mail / Send as Attachment
Application.Dialogs(xlDialogSendMail).Show
End Sub
The first two tasks I need to do within Excel, the third task I could do through Delphi, I just thought it would be nice to do it through Excel since I'm there.

There seems to be several ways to use COM Automation between Delphi and Excel. Most of the examples I've found are for old versions of Excel. That is why I went with TurboPower's OfficePartner on SourceForge as they had a Delphi 2010 version.

Thank you for any help anyone can provide.

[Wolfgan]

Put a file with an example of work. This module was written by me for their own needs. You can easily rasshiret it to their needs.