Transferring Data From An Excel Table To Delphi's Paradox Table

I would like to know how I would be able to use Delphi to startup Microsoft Excel 97 and open a specific existing workbook WITH the capability for a user to work with an opened Excel workbook. Then, through Delphi, I would like to get the data from a table in that specific workbook and tranfer the data to a Paradox table.
kokhooAsked:
Who is Participating?
 
ellessarCommented:
I wrote parts of this for another app recently, and have ripped portions out for your use...

Have fun and good luck!

unit UseExcel;

interface

uses
  SysUtils, FileCtrl, Classes, Dialogs, ComObj, Math ;


function OpenExcel : Boolean;

var
      ExcelApp: Variant;
      NewSheet : Variant;
      XlPos : Integer;
      bFound : Boolean;
      FileName : String;


implementation
// Open excel up if it is closed or
function OpenExcel : Boolean;
var
  nCnt : Integer;
  bFound : Boolean;
  xl : Variant;
  workname, locfilename : String;
begin
  result := false;
  bFound := False;
  // RCL

  try
    // try and look for the excel application in memory and activate it.
    try
      ExcelApp := GetActiveOleObject('Excel.Application');
    except
      // generated if the app does not exist or can not be found, so start
      // a new instance of Excel
      on e:EOleSysError do
      begin
        ExcelApp := CreateOleObject('Excel.Application');
      end;
    end;

    ExcelApp.Visible := True;
    LocFileName := uppercase(ExtractFileName(FileName));
    // arrays start at 1 not 0
    // search for the workbook we are working with.
    for nCnt := 1 to ExcelApp.Workbooks.Count do
    begin
      WorkName := Uppercase(Trim(ExcelApp.Workbooks[ncnt].name));
      if workname = locfilename then
      begin
        bFound := True;
        break;
      end;
    end;
    // If the workbook was not found, open the relevant workbook from disk.
    if bFound = false then
    begin
      //add a new workbook
      xl := ExcelApp.Workbooks.Add;
      //xl.Name := filename after save;
      xl.Saveas(filename);
    end;

    ExcelApp.Workbooks[ExtractFileName(FileName)].activate;
    NewSheet := ExcelApp.Workbooks[ExtractFileName(FileName)].ActiveSheet;

    Newsheet.Name := csRanges;
    // change the widths of the columns for best current presentation
    NewSheet.Columns['A:ZZ'].ColumnWidth := 13.00;
    result := true;
  except
    on e: exception do
    begin
      messagedlg(e.message, mtError, [mbOk], 0);
    end;
  end;
end;

// Used to create a worksheet and set it as active if it is not already active.
Function ExcelSheet(const aName : String; const bSetActive : Boolean) : Boolean;
var nCnt : Integer;
begin
  try
    // re-initialise
    bFound := False;
    // Search for the sheet with the name of the relevant name we are working with.
    For nCnt := 1 to ExcelApp.WorkSheets.Count do
    begin
      if ExcelApp.WorkSheets[nCnt].Name = aName then
      begin
        NewSheet := ExcelApp.WorkSheets[nCnt];
        bFound := True;
        Break;
      end;
    end;

    // if the sheet was not found, create a new one for this name.
    if bFound = false then
    begin
      NewSheet := ExcelApp.Worksheets.Add(aName);
    end;
    // either way, activate it.
    NewSheet.Activate;
    result := True;
  except
    // Handle exception with an error message
    on e: Exception do
    begin
      result := False;
      MessageDlg('Error: ' + e.message, mtError, [mbOk], 0);
    end; // End on e:Exception
  end; // end Try.. Except.. End;
end;

// write a line of information over to excel
// the parameters are: The sheet to use, the part of the app we are in,
// the description to provide and the value to enter.
function WriteToExcel(const aSheet, APart, AName, AVal : String) : Boolean;
var nCnt : Integer;
begin
  try
    result := false;

    ExcelApp.Workbooks[FileName].activate;

    // move down one row
    Inc(XlPos);

    // set the sheet to be the one in use as per the sheet name we were sent.
    ExcelSheet(aSheet, True);

    // Format the cells to be text based to ensure maximum amount
    // of number detail is displayed. The @ symbol corresponds to the text data type in Excel.
    // After that insert the values of the three variables into the cells.
    NewSheet.Range['A'+inttostr(XLPos)].NumberFormat := '@';
    NewSheet.Range['A'+inttostr(XLPos)].Value := APart;
    NewSheet.Range['C'+inttostr(XLPos)].NumberFormat := '@';
    NewSheet.Range['C'+inttostr(XLPos)].Value := AName;
    NewSheet.Range['E'+inttostr(XLPos)].NumberFormat := '@';
    NewSheet.Range['E'+inttostr(XLPos)].Value := AVal;
    result := True;
  except
    // Handle exception with an error message
    on e: Exception do
    begin
      result := false;
      MessageDlg('Error: ' + e.message, mtError, [mbOk], 0);
    end; // End on e:Exception
  end; // end Try.. Except.. End;


end;

function CloseExcel : Boolean;
var ncnt : Integer;
begin
  result := False;
  try
    // save the workbook

    bFound := False;
    // arrays start at 1 not 0
    // search for the workbook we are working with.
    for nCnt := 1 to ExcelApp.Workbooks.Count do
    begin
      if Uppercase(Trim(ExcelApp.Workbooks[ncnt].name)) = ExtractFileName(FileName) then
      begin
        bFound := True;
        break;
      end;
    end;

    if bFound = True then
    begin
      ExcelApp.Workbooks[filename].Save;
      //  This will close the workbook and save it.
      ExcelApp.Workbooks[filename].Close(True);
    end;

    //  quit excel.
    ExcelApp.Quit;      // When you finish, use the Quit method to close

    result := True;
  except
    // Handle exception with an error message
    on e: Exception do
    begin
      result := False;
      MessageDlg('Error: ' + e.message, mtError, [mbOk], 0);
    end; // End on e:Exception
  end; // end Try.. Except.. End;

end;

{End Excel Handling}
end;
0
 
yk030299Commented:
did you try the BatchMove?
0
 
simonetCommented:
There are basically 3 ways to do it:

1) Using the Excel ODBC driver, which lets you treat an Excel worksheet as a table, thus use any data-aware component to manipulate it. The Excel ODBC driver is available from MS and also as part of MS Office.

2) Using the Automation Servers that come with Delphi 5

3) Directly accessing the Excel worksheet using a 3rd party component, such as:

http://www.econos.de/software/borland/aexcel/index.html

Yours,

Alex
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
simonetCommented:
BTW, besides the link I suggested above, here's a much better option:

http://www.borland.com/delphi/papers/microexcel/

Alex
0
 
kokhooAuthor Commented:
Thank you for your comment...It has indeed helped me in starting up Excel from Delphi, but I still have the problem opening a specific workbook for the user to work in and transferring the data from the table in the workbook to a Paradox table...

ODBC is not a possible solution in my current situation...Also, I'm only using Delphi 3...Thus no Automation Server...I'm currently evaluating the third party component from the link you so kindly provided...

Thank you...
0
 
kokhooAuthor Commented:
Thank you for your comment...It has indeed helped me in starting up Excel from Delphi, but I still have the problem opening a specific workbook for the user to work in and transferring the data from the table in the workbook to a Paradox table...

ODBC is not a possible solution in my current situation...Also, I'm only using Delphi 3...Thus no Automation Server...I'm currently evaluating the third party component from the link you so kindly provided...

Thank you...
0
 
kotikCommented:
Hi!
In Delphi 3 you can use something like this:

uses
  ComObj;

procedure TForm1.Button1Click(Sender: TObject);
var
  XL: Variant;
begin
  XL := CreateOleObject('Excel.Application');
  XL.Run;
  XL.Visible := true;
  XL.Workbooks.Open('E:\temp.xls');
end;

This works in Delphi 4, but I think it will work in Delphi 3 too.
To get the function reference for Excel object simply open the Excel 97, then press Alt+F11 (this will open VBA editor), then press F2 and you'll get all functions, procedures and properties you need.

Regards,
Kot

0
 
kokhooAuthor Commented:
Thanks for your program...Some parts of it were relevant to my question...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.