Need to manipulate an Excel file

[everseeker]

I am working with an Excel file... adding/deleting/reatrranging data, etc...etc...etc...

When I am done, I want to set the width of all the cells to be "as wide as they need to be, no more"

I am used to doing this by clicking the blank square in the upper-left of the sheet (Selects all cells), then clicking to auto-resize 1 cell(doubleclick while hovering over the edge of the cell)

I found the "Commands" to do this:

Cntl-A

Format/Row/Autofit

Format/Column/Autofit Selection

ummm... how do I pass this to the spreadsheet I have open for read/write in EXCELCOM_UDF?

[DaRam]

I am working with an Excel file... adding/deleting/reatrranging data, etc...etc...etc...

When I am done, I want to set the width of all the cells to be "as wide as they need to be, no more"

I am used to doing this by clicking the blank square in the upper-left of the sheet (Selects all cells), then clicking to auto-resize 1 cell(doubleclick while hovering over the edge of the cell)

I found the "Commands" to do this:

Cntl-A

Format/Row/Autofit

Format/Column/Autofit Selection

ummm... how do I pass this to the spreadsheet I have open for read/write in EXCELCOM_UDF?

oXLWks.UsedRange.Columns.AutoFit

And

oXLWks.UsedRange.Rows.AutoFit

Where oXLWks is the Excel Worksheet Object (=ActiveSheet)

[everseeker]

oXLWks.UsedRange.Columns.AutoFit

And

oXLWks.UsedRange.Rows.AutoFit

Where oXLWks is the Excel Worksheet Object (=ActiveSheet)

So, given the following, as an example of how I am working with the file:

_ExcelSheetActivate($oExcel, "Output")

is oXLWks=$oExcel or is it "Output" ?

and, can I assume that UsedRange = system defined? or do I need to replace this with a X1Y1 style value?

(Can't find a ref in Help.... is this something that needs an Include file?)

Edited April 28, 2008 by everseeker

[DaRam]

I presume your Worksheet is named "output", in which case:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open("Drive_Path_to_Your_Excel_WorkBook.XLS")
$oExcel.WorkBooks.Sheets("ouput").Select; Change our worksheet name if different here or comment out this line if the first sheet is the one you want
sleep(5000)                                            ; See the results for few seconds
$oExcel.ActiveWorkBook.ActiveSheet.UsedRange.Columns.AutoFit
sleep(5000)                                            ; See the results for few seconds
$oExcel.ActiveWorkBook.ActiveSheet.UsedRange.Rows.AutoFit
sleep(5000)                                            ; See the results for few seconds
$oExcel.ActiveWorkBook.Saved = 1                          ; Simulate a save of the Workbook
$oExcel.Quit                                              ; Quit Excel

[Locodarwin]

Or just use what's in the UDF:

_ExcelColWidthSet($oExcel, "A:IV", "autofit")

_ExcelRowHeightSet($oExcel, "1:999", "autofit")

Replace the "IV" and "999" portions with whatever your last columns and rows are. You can use _ExcelSheetUsedRangeGet() for that.

Good luck with your Excel endeavors.

-S

Edited April 28, 2008 by Locodarwin

[everseeker]

Or just use what's in the UDF:

_ExcelColWidthSet($oExcel, "A:IV", "autofit")

_ExcelRowHeightSet($oExcel, "1:999", "autofit")

Replace the "IV" and "999" portions with whatever your last columns and rows are. You can use _ExcelSheetUsedRangeGet() for that.

Good luck with your Excel endeavors.

-S

Hmmmm... I never saw that option.... Yup, it's there, plain as day...

Sheesh... OK, Got it in now, thanks.

[Not working though..... sigh.... Time 2 Debug ]

[everseeker]

Or just use what's in the UDF:

_ExcelColWidthSet($oExcel, "A:IV", "autofit")

_ExcelRowHeightSet($oExcel, "1:999", "autofit")

Replace the "IV" and "999" portions with whatever your last columns and rows are. You can use _ExcelSheetUsedRangeGet() for that.

Good luck with your Excel endeavors.

-S

Since the output I am getting is:

C:\Documents and Settings\pbm0869\My Documents\Auto IT 3 work\ExcelCOM_UDF.au3 (1993) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Columns($vColumn).Autofit

$oExcel.Activesheet.Columns($vColumn)^ ERROR

->15:05:22 AutoIT3.exe ended.rc:1

+>15:05:23 AutoIt3Wrapper Finished

>Exit code: 1 Time: 838.274

I am inclined to think that the error MAY not be with my script....

The lines I used:

_ExcelSheetActivate($oExcel, "Output")

_ExcelColWidthSet($oExcel, "1:80", "autofit")

[everseeker]

Since the output I am getting is:

C:\Documents and Settings\pbm0869\My Documents\Auto IT 3 work\ExcelCOM_UDF.au3 (1993) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Columns($vColumn).Autofit

$oExcel.Activesheet.Columns($vColumn)^ ERROR

->15:05:22 AutoIT3.exe ended.rc:1

+>15:05:23 AutoIt3Wrapper Finished

>Exit code: 1 Time: 838.274

I am inclined to think that the error MAY not be with my script....

The lines I used:

_ExcelSheetActivate($oExcel, "Output")

_ExcelColWidthSet($oExcel, "1:80", "autofit")

Looking at the notes in the UDF... looks like _ExcelRowHeightSet is only designed for 1 column/row at a time... (ick!)

$iRow - The Interger representation of a valid Excel Row

(umm, the comments are a bit off here... The titles of the field descriptions don't match ($sRow <>$iRow for example))

[DaRam]

Looking at the notes in the UDF... looks like _ExcelRowHeightSet is only designed for 1 column/row at a time... (ick!)

$iRow - The Interger representation of a valid Excel Row

(umm, the comments are a bit off here... The titles of the field descriptions don't match ($sRow <>$iRow for example))

Hi:

I stand by my original suggestion, this will work:

$oExcel.Activesheet.UsedRange.Columns.AutoFit
$oExcel.Activesheet.UsedRange.Rows.AutoFit

[Locodarwin]

Since the output I am getting is:

C:\Documents and Settings\pbm0869\My Documents\Auto IT 3 work\ExcelCOM_UDF.au3 (1993) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Columns($vColumn).Autofit

$oExcel.Activesheet.Columns($vColumn)^ ERROR

->15:05:22 AutoIT3.exe ended.rc:1

+>15:05:23 AutoIt3Wrapper Finished

>Exit code: 1 Time: 838.274

I am inclined to think that the error MAY not be with my script....

The lines I used:

_ExcelSheetActivate($oExcel, "Output")

_ExcelColWidthSet($oExcel, "1:80", "autofit")

Yes, it is. You're attempting to address rows with the wrong function. Use _ExcelRowHeightSet() for rows (1:80) and _ExcelColWidthSet() for columns (A:ZZ).

-S

[Locodarwin]

Hi:

I stand by my original suggestion, this will work:

$oExcel.Activesheet.UsedRange.Columns.AutoFit
$oExcel.Activesheet.UsedRange.Rows.AutoFit

FYI, "UsedRange" is unreliable and buggy, according to many experts. The recommended approach for returning the used range is found in _ExcelSheetUsedRangeGet().

-S

[DaRam]

FYI, "UsedRange" is unreliable and buggy, according to many experts. The recommended approach for returning the used range is found in _ExcelSheetUsedRangeGet().

-S

I accept that it is unreliable, but in this context/use (to autofit) it will just work.