Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2000-03-09
8
Medium Priority
?
530 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 300 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Loops Section Overview

772 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