Jump to content

Need to manipulate an Excel file


Recommended Posts

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

Link to comment
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)

Link to comment
Share on other sites

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

Link to comment
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
Link to comment
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

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]
Link to comment
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

Link to comment
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

Link to comment
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

Link to comment
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
Link to comment
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]
Link to comment
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]
Link to comment
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.
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...