Solved

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

Posted on 2000-03-09
8
507 Views
Last Modified: 2010-04-04
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.
0
Comment
Question by:kokhoo
8 Comments
 
LVL 1

Expert Comment

by:yk030299
ID: 2603275
did you try the BatchMove?
0
 
LVL 15

Expert Comment

by:simonet
ID: 2603396
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
 
LVL 15

Expert Comment

by:simonet
ID: 2603428
BTW, besides the link I suggested above, here's a much better option:

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

Alex
0
 

Author Comment

by:kokhoo
ID: 2603488
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:kokhoo
ID: 2603491
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
 
LVL 2

Expert Comment

by:kotik
ID: 2603695
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
 

Accepted Solution

by:
ellessar earned 150 total points
ID: 2603793
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
 

Author Comment

by:kokhoo
ID: 2618110
Thanks for your program...Some parts of it were relevant to my question...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now