« previous next »
Pages: [1]   Go Down

Author Topic: Interacting with Excel  (Read 13818 times)

richardh

  • Jr. Member
  • **
  • Posts: 54
Interacting with Excel
« 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


Logged

stanl

  • Pundit
  • *****
  • Posts: 1777
Re: Interacting with Excel
« Reply #1 on: June 17, 2013, 09:44:18 am »
Go into the Tech Support Database and search for 'autofit' - tutorial and several examples.
Logged

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Interacting with Excel
« Reply #2 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
Logged 
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #3 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
Logged

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #4 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
Logged

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Interacting with Excel
« Reply #5 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
Logged 
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #6 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.
Logged

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #7 on: June 17, 2013, 11:07:03 am »
That did the trick Deana,

Thanks!!!

Logged

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Interacting with Excel
« Reply #8 on: June 17, 2013, 11:14:41 am »
Here is a code sample that should handle the autofit:
Code: Winbatch
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
Logged 
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #9 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
Logged

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #10 on: June 17, 2013, 12:28:46 pm »
Logged

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #11 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
Logged

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Interacting with Excel
« Reply #12 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
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
Logged 
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #13 on: June 17, 2013, 03:24:23 pm »
Thanks Deana  ;D

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

RH
Logged

td

  • Tech Support
  • *****
  • Posts: 4293
    • WinBatch
Re: Interacting with Excel
« Reply #14 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.
Logged
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #15 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
Logged

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Interacting with Excel
« Reply #16 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:
Code: [Select]
Columns("B:B").Select
Selection.NumberFormat = "m/d/yyyy"
Logged 
Deana F.
Technical Support
Wilson WindowWare Inc.

richardh

  • Jr. Member
  • **
  • Posts: 54
Re: Interacting with Excel
« Reply #17 on: June 19, 2013, 06:52:40 am »
 ::) Now why didn't I think of that?

Thanks,
D
Logged
Pages: [1]   Go Up
« previous next »