WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: richardh on June 17, 2013, 09:33:45 AM

Title: Interacting with Excel
Post by: 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


Title: Re: Interacting with Excel
Post by: stanl on June 17, 2013, 09:44:18 AM
Go into the Tech Support Database and search for 'autofit' - tutorial and several examples.
Title: Re: Interacting with Excel
Post by: Deana on June 17, 2013, 10:28:29 AM
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
Title: Re: Interacting with Excel
Post by: richardh on June 17, 2013, 10:39:05 AM
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
Title: Re: Interacting with Excel
Post by: 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
Title: Re: Interacting with Excel
Post by: Deana on June 17, 2013, 10:59:48 AM
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
Title: Re: Interacting with Excel
Post by: richardh on June 17, 2013, 11:03:08 AM
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.
Title: Re: Interacting with Excel
Post by: richardh on June 17, 2013, 11:07:03 AM
That did the trick Deana,

Thanks!!!

Title: Re: Interacting with Excel
Post by: Deana on June 17, 2013, 11:14:41 AM
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
Title: Re: Interacting with Excel
Post by: 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
Title: Re: Interacting with Excel
Post by: richardh on June 17, 2013, 12:28:46 PM
Found this list of constants...

http://techsupt.winbatch.com/ts/T000001033005F9.html
Title: Re: Interacting with Excel
Post by: richardh on June 17, 2013, 01:04:08 PM
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
Title: Re: Interacting with Excel
Post by: Deana on June 17, 2013, 01:13:44 PM
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
Title: Re: Interacting with Excel
Post by: richardh on June 17, 2013, 03:24:23 PM
Thanks Deana  ;D

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

RH
Title: Re: Interacting with Excel
Post by: td on June 18, 2013, 07:35:13 AM
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.
Title: Re: Interacting with Excel
Post by: 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
Title: Re: Interacting with Excel
Post by: Deana on June 18, 2013, 09:47:02 AM
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"
Title: Re: Interacting with Excel
Post by: richardh on June 19, 2013, 06:52:40 AM
 ::) Now why didn't I think of that?

Thanks,
D