delphi + excel

HMann,

OpenText will open a new work sheet, so it isn't really what your after. You could use the QueryTables to import the data, but it is fairly simple if you just want to work with the range object. The example (function) below loads a CSV file, calculates the rows and columns, and then imports this data into your worksheet exactly where you want it (say for example, at cell A1).

Hope this helps,
Russell

-------

procedure MergeCSVFile(WorkSheet: Variant; StartCell: String; FileName: String);
var  Range:      Variant;
     tsFile:     TStringList;
     tsLine:     TStringList;
     vaRange:    Variant;
     dwCount:    Integer;
     dwRows:     Integer;
     dwCols:     Integer;
     dwRow:      Integer;
     dwCol:      Integer;

     function Max(I1, I2: Integer): Integer;
     begin
        if (I1 > I2) then
           result:=I1
        else
           result:=I2;
     end;

begin

  // Create string lists to the hold the file and one to parse the lines in the file
  tsFile:=TStringList.Create;
  tsLine:=TStringList.Create;
  try
     // Attempt to load the file
     tsFile.LoadFromFile(FileName);
     // Calculate the number of rows and max columns in the file
     dwRows:=0;
     dwCols:=0;
     // Iterate the lines
     for dwCount:=0 to Pred(tsFile.Count) do
     begin
        // Check for blank lines which will not get included
        if (Length(Trim(tsFile[dwCount])) > 0) then
        begin
           // Increment the row count
           Inc(dwRows);
           // Clear the line parser
           tsLine.Clear;
           // Set the comma text
           tsLine.CommaText:=tsFile[dwCount];
           // Get the max column count
           dwCols:=Max(dwCols, tsLine.Count);
        end;
     end;
     // If no rows or cols then nothing to do
     if (dwRows > 0) and (dwCols > 0) then
     begin
        // Create variant array to hold the range data
        vaRange:=VarArrayCreate([0, Pred(dwRows), 0, Pred(dwCols)], varVariant);
        try
           // Fill the array in
           dwRow:=0;
           for dwCount:=0 to Pred(tsFile.Count) do
           begin
              // Break if row count has been hit
              if (dwRow = dwRows) then break;
              // Check for blank lines which will not get included
              if (Length(Trim(tsFile[dwCount])) > 0) then
              begin
                 // Clear the line parser
                 tsLine.Clear;
                 // Set the comma text
                 tsLine.CommaText:=tsFile[dwCount];
                 // Set the columns
                 for dwCol:=0 to Pred(dwCols) do
                 begin
                    if (dwCol >= tsLine.Count) then
                       vaRange[dwRow, dwCol]:=''
                    else
                       vaRange[dwRow, dwCol]:=tsLine[dwCol];
                 end;
                 // Increment the row counter
                 Inc(dwRow);
              end;
           end;
           // Get the range and resize it
           Range:=WorkSheet.Range[StartCell].Resize[dwRows, dwCols];
           // Set the variant array data to the desired range
           Range.Value:=vaRange;
        finally
           // Clear the variant data
           VarClear(vaRange);
        end;
     end;
  finally
     // Free the string lists
     tsLine.Free;
     tsFile.Free;
  end;

end;

procedure TForm1.Button1Click(Sender: TObject);
var  OleApplication:   Variant;
     OleWorkBook:      Variant;
     OleWorkSheet:     Variant;
begin

  OleApplication:=CreateOleObject('Excel.Application');
  OleApplication.Visible:=True;
  OleWorkBook:=OleApplication.Workbooks.open('c:\temp\book1.xls');
  OleWorkSheet:=OleApplication.ActiveSheet;

  // Param1 - Worksheet to import data into
  // Param2 - Upper/Left cell to start the import at
  // Param3 - CSV file to import in

  MergeCSVFile(OleWorkSheet, 'A2', 'c:\temp\test.csv');

  OleApplication.Quit;
  OleApplication:=Unassigned;

end;