?
Solved

Excel: zoom or filter with OLE

Posted on 2012-03-20
2
Medium Priority
?
1,513 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
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 …
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…

800 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