Sign in to follow this  
Followers 0
everseeker

Need to manipulate an Excel file

12 posts in this topic

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?


Everseeker

Share this post


Link to post
Share on other sites



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)

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by everseeker

Everseeker

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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. :D

-S

Edited by Locodarwin

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

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. :D

-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

Share this post


Link to post
Share on other sites

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. :D

-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

Share this post


Link to post
Share on other sites

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))


Everseeker

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

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


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0