Autoresize / Autofit in Excel

[freggel99]

Hello,

How can i use the autoresize/ autofit function in Excel through QM, i tried to understand the help but cannot get a clue what it means. Also the MSDN doesn't help me much. Please give me an example (helps me to understand) or explain what VARIANT Autofit() means,


TIA

Freggel

[Gintaras]

Lets use ExcelSheet class to connect to Excel. Declare a variable and connect to the active sheet in Excel:

ExcelSheet es.Init

Excel functions can be accessed through ws:

es.ws.HereIsDisplayedListOfFunctionsOfWorksheet

But AutoFit does not belong to Worksheet class. It belongs to Range class. To get Range, can be used several functions of Worksheet. Let's use function Range for example:

Excel.Range r=es.ws.Range("A:C") ;;now r represents columns A to C
r.HereIsDisplayedListOfFunctionsOfRange

And here it is:

r.AutoFit

VARIANT is type of AutoFit's return value. It is not documented, so don't use it. But if using, it can be assigned to a variable of VARIANT type and maybe of some other type:

VARIANT v
v=r.UsedRange

Complete code:

Code:

Copy      Help

ExcelSheet es.Init
Excel.Range r=es.ws.Range("A:C")
r.AutoFit

In my tests, AutoFit generated error. Maybe data or some settings must be different, don't know. But AutoFill works:

Code:

Copy      Help

ExcelSheet es.Init
Excel.Range sourceRange=es.ws.Range("A1:A2")
Excel.Range fillRange = es.ws.Range("A1:A20")
sourceRange.AutoFill(fillRange 0)

I took this example from Excel help (not from MSDN but from local Excel help):
AutoFill Method
See AlsoApplies ToExampleSpecificsPerforms an autofill on the cells in the specified range. Variant.

expression.AutoFill(Destination, Type)
expression Required. An expression that returns one of the objects in the Applies To list.

Destination Required Range object. The cells to be filled. The destination must include the source range.

Type Optional XlAutoFillType. Specifies the fill type.

XlAutoFillType can be one of these XlAutoFillType constants.
xlFillDays
xlFillFormats
xlFillSeries
xlFillWeekdays
xlGrowthTrend
xlFillCopy
xlFillDefault default
xlFillMonths
xlFillValues
xlFillYears
xlLinearTrend
If this argument is xlFillDefault or omitted, Microsoft Excel selects the most appropriate fill type, based on the source range.

Example
This example performs an autofill on cells A1:A20 on Sheet1, based on the source range A1:A2 on Sheet1. Before running this example, type 1 in cell A1 and type 2 in cell A2.

Set sourceRange = Worksheets("Sheet1").Range("A1:A2")
Set fillRange = Worksheets("Sheet1").Range("A1:A20")
sourceRange.AutoFill Destination:=fillRange