Interacting with Excel

[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]

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

[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]

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

[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 Expand

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

[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]

Found this list of constants...

http://techsupt.winbatch.com/ts/T000001033005F9.html

[richardh]

Jeez! ???

I've went through a bunch of code examples and couldn't find a single example of anyone aligning data in excel.
No luck with google searching for a WinBatch example either.

http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials/OLE~TUTORIAL+Automating~Excel~Tutorial.txt

[Deana]

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 Expand

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

[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.

[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]

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:

Code Select Expand


Columns("B:B").Select
Selection.NumberFormat = "m/d/yyyy"


[richardh]

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

Thanks,
D