Excel: zoom or filter with OLE

Hi to the community
I am trying to programmatically create an excel file (using Delphi 6) under Windows XP

I have difficulties in two points

1. How to implement zoom in a sheet?
2. How to activate excel filters?

I would like to ask some help about.

An abstract of the source code I use is here:


uses ComObj;

var
  ExcelApp: OleVariant;
  ColumnRange, Sheet: OleVariant;

begin
  ExcelApp := CreateOLEObject ('Excel.Application');

  ExcelApp.Workbooks.Add; // Add a new workbook
  ExcelApp.Workbooks[1].Sheets.Add (,,2,xlWorkSheet); // Add 2 new sheets
  ExcelApp.Workbooks[1].Sheets[1].Name := 'Cars';

  // Change column width
  Sheet := ExcelApp.Workbooks[1].WorkSheets ['Cars'];
  ColumnRange := Sheet.Columns;
  ColumnRange.Columns [2].ColumnWidth := 20;

  ExcelApp.Workbooks[1].WorkSheets ['Cars'].Activate;
  Sheet.Range['E1', 'E1'].EntireColumn.NumberFormat := '@'; // text

  // ------------------------- 1st point - Set zoom percentage ------------------------------------
  // Next commented statement does not work.
  // ExcelApp.ActiveWindow.PageSetup.Zoom := 80;
  // It produces run-time error: Method 'PageSetup' not supported by automation object

  // Next uncommented statement does not produce any compilation or execution error
  Sheet.PageSetup.Zoom := 80;
  // Although it gives no result... no zoom modified

{ ---------------------- The 2nd point is: -------------------------------------------------
  When setting an excel sheet, how could I implement filters through automation object?
  Excel menu: Data | Filter | AutoFilter
}

  // Fill data
  Sheet.Cells [1, 2] := 'Make';
  Sheet.Cells [3, 2] := 'Opel';

  ExcelApp.Visible := true; // Show excel
  ExcelApp.DisplayAlerts := false; // Discard unsaved files...
  ExcelApp.ActiveWorkBook.SaveAs ('Cars.xls');
end.

Is any proposition to solve these points?

Thank you for your attention
Nikiforos
nikifAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
ExcelApp.ActiveSheet.PageSetup.Zoom := 80; <- would this work?

ExcelApp.ActiveSheet.Range("A:B").AutoFilter; <- would this work?  There are other paramters for autofilter, but this should set the default data filter and if that works, I'm sure it would accept the appropriate parameters beyond that if needed.

OTHERWISE - a long shot, but perhaps....

Can you access the VBProject object from Delphi?  If so, you could just import a .Bas module and run the macro to do all your work from there.


OR - perhaps this would work.

Rather than create a new workbook, first create a workbook that has some of these features you need, then have your code take that workbook, make a copy and then do the rest of the work you need done to it.  OR - start with an existing workbook that has some macros that you would call from Delphi to finish off the workbook???  Your last macro could be to save as .XLSX or whatever to rid the macros if needed.

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nikifAuthor Commented:
Thank you Dave
All works fine

Concerning AutoFilter, next statement works fine
  Sheet.Range['A1:H1'].AutoFilter; // activate AutoFilter

Concerning zoom, I tried more in the meantime and I noticed that
a) there is a zoom referring the menu command File|Page Setup|Scaling|Adjust to xx%, which acts to the zoom print
  Sheet.PageSetup.Zoom := 70;

b) there is a zoom referring the zoom percentage of the tabsheet in screen
  ExcelApp.ActiveWindow.Zoom := 80; // Acts to tabsheet zoom


Thank you for your solution
Nikiforos
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Pascal

From novice to tech pro — start learning today.