Solved

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

Posted on 2000-03-09
8
516 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

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…
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month5 days, 1 hour left to enroll

635 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