Pascal and Delphi

Pascal and Delphi RSS

Integating Paradox table into MS-Excel 2007

Please support our Pascal and Delphi advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Nov 2007

Posts: 16

Reputation: delphiman is an unknown quantity at this point

Solved Threads: 0

delphiman

Offline

Newbie Poster

Integating Paradox table into MS-Excel 2007

Jul 2nd, 2008

I have the following code from Mastering Delphi 6 by Marco Cantu [Page 875] as supplied in the CD supplied with the book [more particularly a Project called "Office"] which I have got to work very well in transporting data created by my Delphi 6 Application into an Excel "PivotFile". To which I relate a prepared spreadsheet of my own.

My only problems with the project are
1.
It always leaves me with only the one option of filing the created Excel file to a file called "Book1".

I would prefer to control the name of the file that Excel creates - to be something of my own choosing; but I don't see HOW to do that from the code: indeed I don't even see how it comes about that a file to be called "Book 1" is forced.

Is there some coding-genius out there who can please throw some light on this?

2
If I create a fresh file from this code from an updated version of my Paradox table in this manner the NEW Book1 will obviousely overwrite the original Book1 - so that older infomation [in the original Book1] is deleted.

I would prefer that a fresh "Book1" merely UPDATES the FIELDS in the old "Book1" - but cannot see how to bring that about.

Is there some coding-genius out there who can please throw some light on this?

Thanks in advance.
delphiman

 Help with Code Tags 
 Pascal and Delphi Syntax (Toggle Plain Text) 
 // create and show
    ExcelApplication1.Visible [0] := True;
    ExcelApplication1.Workbooks.Add (NULL, 0);
    // fill is the first row with field titles
    RangeE := ExcelApplication1.ActiveCell;
    for I := 0 to tblMyTotals.Fields.Count - 1 do
    begin
      RangeE.Value := tblMyTotals.Fields [I].DisplayLabel;
      RangeE := RangeE.Next;
    end;
    // add field data in following rows
     tblMyTotalsDisableControls;
  try
    Bookmark := MyTotals.Bookmark;
    try
      tblMyTotals.First;
      Row := 2;
      while not tblMyTotals.EOF do
      begin
        RangeE := ExcelApplication1.Range ['A' + IntToStr (Row),
          'A' + IntToStr (Row)];
        for I := 0 to tblMyTotals.Fields.Count - 1 do
        begin
          RangeE.Value := tblMyTotals.Fields [I].AsString;
          RangeE := RangeE.Next;
        end;
        tblMyTotals.Next;
        Inc (Row);
      end;
    finally
      tblMyTotals.Bookmark := Bookmark;
    end;
  finally
    tblMyTotals.EnableControls;
  end;
    // format the section
    RangeE := ExcelApplication1.Range ['A1', 'E' + IntToStr (Row - 1)];
    RangeE.AutoFormat (3, NULL, NULL, NULL, NULL, NULL, NULL);
  end;
 // create and show
    ExcelApplication1.Visible [0] := True;
    ExcelApplication1.Workbooks.Add (NULL, 0);
    // fill is the first row with field titles
    RangeE := ExcelApplication1.ActiveCell;
    for I := 0 to tblMyTotals.Fields.Count - 1 do
    begin
      RangeE.Value := tblMyTotals.Fields [I].DisplayLabel;
      RangeE := RangeE.Next;
    end;
    // add field data in following rows
     tblMyTotalsDisableControls;
  try
    Bookmark := MyTotals.Bookmark;
    try
      tblMyTotals.First;
      Row := 2;
      while not tblMyTotals.EOF do
      begin
        RangeE := ExcelApplication1.Range ['A' + IntToStr (Row),
          'A' + IntToStr (Row)];
        for I := 0 to tblMyTotals.Fields.Count - 1 do
        begin
          RangeE.Value := tblMyTotals.Fields [I].AsString;
          RangeE := RangeE.Next;
        end;
        tblMyTotals.Next;
        Inc (Row);
      end;
    finally
      tblMyTotals.Bookmark := Bookmark;
    end;
  finally
    tblMyTotals.EnableControls;
  end;
    // format the section
    RangeE := ExcelApplication1.Range ['A1', 'E' + IntToStr (Row - 1)];
    RangeE.AutoFormat (3, NULL, NULL, NULL, NULL, NULL, NULL);
  end;

•

Join Date: Nov 2007

Posts: 16

Reputation: delphiman is an unknown quantity at this point

Solved Threads: 0

delphiman

Offline

Newbie Poster

Re: Integating Paradox table into MS-Excel 2007

Jul 8th, 2008

From the number of views to this thread it is obviously attracting some interest so for those interested I would like to add that I have solved the 2nd part of my problem as follows.

I have created 4 pivot Excel Tables to which my Excel project relates. Each pivot table representing 3 months of the year. Whereby "Interface1" is for period March - May, "Interface2" is for period June - Aug ... etc..

Having created "Book 1" for the quarter concered as per the code referred to in this thread I manually change the name of "Book 1" accordingly and save it to relevant "Interface" file - overwriting it.

A bit laborious - but it works. I would much prefer to control that the "Book 1" file created by the code is named "Interface1" (whatever) as is relevant per my Delphi code.

So part 1 of my thread remains.

Quick Reply to Thread

Show Printable Version

Other Threads in the Pascal and Delphi Forum

Previous Thread: cxGrid row colour Change
Next Thread: Delphi SetLength dilemma

Thread Tools	Search this Thread
Show Printable Version Email this Page

DaniWeb Message

Cancel Changes