Solved

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

Posted on 2000-03-09
8
511 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
delphi prevent click fast 2 242
Correct Component for Shopping Cart. 2 112
Delphi: ForceDirectory plain function 7 42
RESTRequest Parameter 4 43
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

820 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