Interacting with Excel

Started by richardh, June 17, 2013, 09:33:45 AM

Previous topic - Next topic

richardh

Can someone show me an example of auto fit for Excel.
Tried the following but it doesn't seem to work:  objActiveSheet.Range("A3:J3").EntireColumn.Autofit
(*Note row 1&2 are really long cells which is why I start format at row 3)

As an example lets say I wanted to auto fit the 1st 10 columns
and then change the values in the 5th column to a $currency format.

Thanks In Advance,
RH



stanl

Go into the Tech Support Database and search for 'autofit' - tutorial and several examples.

Deana

Quote from: richardh on June 17, 2013, 09:33:45 AM
Can someone show me an example of auto fit for Excel.
Tried the following but it doesn't seem to work:  objActiveSheet.Range("A3:J3").EntireColumn.Autofit
(*Note row 1&2 are really long cells which is why I start format at row 3)

As an example lets say I wanted to auto fit the 1st 10 columns
and then change the values in the 5th column to a $currency format.

Thanks In Advance,
RH

I would think that objActiveSheet.Range("A3:J3").EntireColumn.Autofit should work. What happens when you try that.

Here is a good resource to help get you started:
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials/OLE~TUTORIAL+Automating~Excel~Tutorial.txt

Here is an example that can be used to change to currency format:
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Change~Currency~Formats~in~Excel.txt
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

Found this example to change format but still no luck on the auto fit issue

objXL = ObjectOpen("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open("C:\Users\RH\Documents\WB Excel Testing\Mon4.csv")


objColumns=objXL.Columns("E:E")
objColumns.Select

objSelection=objXL.Selection
objSelection.NumberFormat = "$#,##0"

ObjectClose(objSelection)
ObjectClose(objColumns)
ObjectClose(objWorkbooks)
ObjectClose(objXL)

Message("Notice","Column A - format successfully changed")
exit

richardh

objXL = ObjectOpen("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open("C:\Users\RH\Documents\WB Excel Testing\Mon4.csv")

;objActiveSheet = objWorkbooks.Workbooks.ActiveSheet

;
objXL.Range("C3:P3").EntireColumn.Autofit ; *****!!! Is there a substitute for EntireColumn that will format only rows below??? !!!********

; I have a really long cell (B) in row 1 and 2 that is giving me the problem - hence the C3 start...

ObjectClose(objSelection)
ObjectClose(objColumns)
ObjectClose(objWorkbooks)
ObjectClose(objXL)

Message("Notice","Auto fit - format successfully changed")
exit

Deana

Quote from: richardh on June 17, 2013, 10:46:41 AM
objXL = ObjectOpen("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open("C:\Users\RH\Documents\WB Excel Testing\Mon4.csv")

;objActiveSheet = objWorkbooks.Workbooks.ActiveSheet

;
objXL.Range("C3:P3").EntireColumn.Autofit ; *****!!! Is there a substitute for EntireColumn that will format only rows below??? !!!********

; I have a really long cell (B) in row 1 and 2 that is giving me the problem - hence the C3 start...

ObjectClose(objSelection)
ObjectClose(objColumns)
ObjectClose(objWorkbooks)
ObjectClose(objXL)

Message("Notice","Auto fit - format successfully changed")
exit

Try 'Columns' instead of EntireColumn.
http://msdn.microsoft.com/en-us/library/office/ff820840.aspx
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

Additionally:

Is there a quick trick to convert a number to letter range for excel?

X axis will vary and range up to ( NT ) or 384

***Update***
*** It appears that if I use the maximum range that it handles all the smaller ranges too... so no calculation necessary.

richardh

That did the trick Deana,

Thanks!!!


Deana

Here is a code sample that should handle the autofit:
Code (winbatch) Select
objXL = ObjectCreate("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open('C:\TEMP\Data\Excel\Authors.xlsx')

objXL.Range("C3:P200").Select
objXL.Selection.Columns.AutoFit
objColumns = 0
objWorkbooks = 0
objXL = 0

Message("Notice","Auto fit - format successfully changed")
Exit
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

In Perl you can center horizontal alignment with...

$sheet->Range($range)->{HorizontalAlignment} = xlHAlignCenter

WinBatch Example ????

objXL = ObjectOpen("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open("C:\temp\timetest4.csv")

objXL.Range("A3:NT3").Columns. ????                  ; xlHAlignCenter

ObjectClose(objSelection)
ObjectClose(objColumns)
ObjectClose(objWorkbooks)
ObjectClose(objXL)

Message("Notice","Column alignment centered successfully")
exit


richardh


Deana

Quote from: richardh on June 17, 2013, 12:03:32 PM
In Perl you can center horizontal alignment with...

$sheet->Range($range)->{HorizontalAlignment} = xlHAlignCenter

WinBatch Example ????

objXL = ObjectOpen("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open("C:\temp\timetest4.csv")

objXL.Range("A3:NT3").Columns. ????                  ; xlHAlignCenter

ObjectClose(objSelection)
ObjectClose(objColumns)
ObjectClose(objWorkbooks)
ObjectClose(objXL)

Message("Notice","Column alignment centered successfully")
exit

it can be accomplished like this. Notice the use of ObjectConstantsGet

Code (winbatch) Select
objXL = ObjectCreate("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open('C:\TEMP\Data\Excel\Authors.xlsx')
constant = ObjectConstantsGet(objXL)
objXL.Range("C3:P200").HorizontalAlignment = constant.xlHAlignCenter
objColumns = 0
objWorkbooks = 0
objXL = 0
Pause('HorizontalAlignment','AlignCenter Complete')
Exit
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

Thanks Deana  ;D

It's already helped me solve some other issues by defining constant.

RH

td

The function ' ObjectConstantsGet' precaches all the constants recognized by all the interfaces supported by all the coclasses of a COM Automation server object.  In the case of the Excel Application object, there are a lot of constants and it can take a bit of time for the function to process them all.  Of course, time can be relative so you may or may not find this to be an issue.  If you do find it to be problematic, you may want to consider creating a variable with a meaningful name and assign it the value of the constant.  That way your script doesn't  have to call the 'ObjectConstantsGet' function every time it is executed.  The down side is that hard coding a constant could break your script if MSFT changes the value of a constant in a future Excel version.  COM Automation programming guidelines make it clear that this should not happen but there is the possibility - MSFT has been known to not follow their own guidelines.

Of course, you can use 'ObjectConstantsGet' to determine the value of the constants of interest or you can use the WIL Type Viewer tool to search Excel's type information of the values of interest.  The WIL Type Viewer tool is shipped with WinBatch and can be launched from WinBatch Studio by clicking on the light blue owl with a raised wing on the tool bar.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

richardh

Tried a bunch of variations with date...
;
constant = ObjectConstantsGet(objXL)
objXL.Range("F3:F3").Columns. :o ; Is there a simple way to specify this column/range as date format mm/dd/yyyy?

;Similar to this...
objXL.Range("D3:D100").Columns.NumberFormat = "$#,##0.00;$#,##0.00"

Thanks,
RH

Deana

Quote from: richardh on June 18, 2013, 08:17:51 AM
Tried a bunch of variations with date...
;
constant = ObjectConstantsGet(objXL)
objXL.Range("F3:F3").Columns. :o ; Is there a simple way to specify this column/range as date format mm/dd/yyyy?

;Similar to this...
objXL.Range("D3:D100").Columns.NumberFormat = "$#,##0.00;$#,##0.00"

Thanks,
RH

One trick I use to determine what methods and properties are available is to record macro in Excel, then view the macros source code. For Example here is the recorded macro in Excel:

Columns("B:B").Select
Selection.NumberFormat = "m/d/yyyy"
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

 ::) Now why didn't I think of that?

Thanks,
D