Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel: zoom or filter with OLE

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Simple Linear Regression

604 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