Solved

Excel: zoom or filter with OLE

Posted on 2012-03-20
2
1,480 Views
Last Modified: 2012-03-24
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
0
Comment
Question by:nikif
[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
2 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37760132
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
 

Author Closing Comment

by:nikif
ID: 37761062
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

729 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