Using Excel (VBA) in Delphi

[TheBugSlayer]

Hi.
Can anyone give me a walktrough or some samples on how to use VBA or to manipulate a spreadsheet in Delphi 6?

Here is a little procedure I would like to convert to Delphi.

[COLOR=red]
Sub DCCenter()

Application.DisplayAlerts = False

    ChDir "C:\My Projects\DupPay2\CurrentSys"
    Workbooks.OpenText Filename:="C:\My Projects\DupPay2\CurrentSys\duhdc", _
        Origin:=xlWindows, StartRow:=6, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 1), Array(1, 2), Array(10, 3), Array(18, 2), Array(74, 2), Array(90, 2), _
        Array(106, 9), Array(130, 2))
        

    Range("B1").Select
    Selection.Cut
    Range("C1").Select
    ActiveSheet.Paste


Dim y As Integer
y = 1

Dim Current As Range
    
While Cells.Item(y, 1) <> &quot;&quot;

    Set Current = Cells.Item(y, 1)
    
    If IsDate(Cells.Item(y, 3)) Then
    Cells.Item(y, 11).Value = &quot;DC&quot;
    Cells.Item(y, 12).Value = Cells.Item(1, 3).Value
    Else
         Cells.Item(y, 3).EntireRow.Delete
         y = y - 1
    End If
    
    y = y + 1
    
Wend

Rows(&quot;1:1&quot;).Select
Selection.Delete Shift:=xlUp
y = 2

While Cells.Item(y, 1) <> &quot;&quot;
    
    Range(Cells(y, 3), Cells(y, 4)).Select
    
    Selection.Copy
    ActiveSheet.Paste Destination:=Cells(y - 1, 8)
                  
     Cells(y, 3).EntireRow.Delete
    
    y = y + 1

Wend

    Columns(&quot;I:I&quot;).Select
    Selection.TextToColumns Destination:=Range(&quot;I1&quot;), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(33, 2))
    Columns(&quot;E:H&quot;).Select
    Selection.Insert Shift:=xlToRight
    Columns(&quot;D:D&quot;).Select
    Selection.TextToColumns Destination:=Range(&quot;D1&quot;), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(17, 2), Array(20, 2), Array(23, 2), Array(41, 2))
    Columns(&quot;A:A&quot;).Select
    Selection.Delete Shift:=xlToLeft
    Columns(&quot;L:L&quot;).Select
    Selection.Replace What:=&quot;EFT&quot;, Replacement:=&quot;&quot;, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range(&quot;A1:O1&quot;).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
Application.DisplayAlerts = True

AppActivate &quot;Duplicate Payment DB&quot;
'SendKeys &quot;{Enter}&quot;
'SendKeys &quot;%EA&quot;
'SendKeys &quot;%EN&quot;
'SendKeys &quot;{Enter}&quot;
'SendKeys &quot;%FC&quot;

End Sub
[/color]

Thank you.

 

[ChrisGSchultz]

Check out the excellent FAQ How to export data to Excel in this forum. The example works well, and with some fidling, you can open existing files (with Excel XP) viz:

[tt]
if not FileExists(fExcelFile) then
begin
SheetsInNewWorkbook[LCID] := 1; // add just 1 worksheet
WorkBooks.Add(EmptyParam, LCID); // Create the workbook
end
else
begin // add a new worksheet to the existing file
WorkBooks.Open(fExcelFile, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam,
LCID);
WorkSheets.Add(EmptyParam, EmptyParam, 1, xlWorksheet, LCID); // add 1 sheet
end; // if FileExists
[/tt]

Similarly, to save the file:
[tt]
if not FileExists(fExcelFile) then
ActiveWorkbook.SaveAs(fExcelFile,
xlNormal, '', '', false, false, xlNochange, false,
xlUserResolution,TRUE,EmptyParam,EmptyParam, LCID)
else
ActiveWorkBook.Save(LCID);
Quit; // bye bye excel
[/tt]
The only proviso I'd make is allow for the version of Excel your using, as the function parameters change with each version thanks to M$

To get a full list of parameters and how they match your VB function (which seem to translate fairly well), you should have a look at:
[tt]\Program Files\Borland\Delphi6\Ocx\Servers\ExcelXX.pas[/tt]
where [tt]XX[/tt] is your Excel version (eg 97, 2000, XP). Its a bit of reading, but it certainly helps when you need to find the correct parameters

You should also have a look at Deborah Pate's pages on automation with Delphi:

http://www.djpate.freeserve.co.uk/AutoExcl.htm

Hope thats some help.

Chris ;-)