Solved

Excel: zoom or filter with OLE

Posted on 2012-03-20
2
1,386 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
2 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now