WinBatch® Technical Support Forum

Archived Boards => COM Automation and dotNet => Topic started by: stanl on July 29, 2013, 12:13:40 PM

Title: Excel Date Conversion - revisted
Post by: stanl on July 29, 2013, 12:13:40 PM
I put together some code which automated and sped up the process of converting a 'general' format AM/PM date text to a fraction of an hour

Code (WINBATCH) Select

   ad = oWS.UsedRange.Address()
   ad = ItemExtract(2,ad,":")
   oWS.Range("$F$1:%ad%").Select
   s=oXL.Selection
   s.NumberFormat="0.00000000"
   ForEach c in s
      c.Value = c.Value * 24
   Next



A user on a VBA site suggested I could eliminate the loop (which does take a bit on 10,000 or more rows with the code below. Have worked with VBA's evaluate, but not sure how to address a WITH block or how to handle .Address in the function.

With Range("A1:A100")
.NumberFormat = "0.00000000"
.Value = Evaluate("=" & .Address & "*24")
End With
Title: Re: Excel Date Conversion - revisted
Post by: Deana on July 29, 2013, 01:23:52 PM
Usually the OBJECT referenced after the WITH should be used with each of the elements in the WITH...END WITH.

For example:

Code (winbatch) Select
;UNDEBUGGED
objRange = objXL.Range("A1:A100")
objRange .NumberFormat = "0.00000000"
this= "=" : objRange.Address : "*24"
objRange .Value = oXL.Evaluate(this)


Title: Re: Excel Date Conversion - revisted
Post by: td on July 29, 2013, 02:01:27 PM
Guesstimate.

Code (winbatch) Select

s=oXL.Selection
s.NumberFormat="0.00000000"
s.Value = oXL.Evaluate(:"=":s.Address:"*24")
Title: Re: Excel Date Conversion - revisted
Post by: stanl on July 29, 2013, 02:24:56 PM
Deana, Tony...

At first I tried both examples and got invalid results, but after inserting the initial : in the Evaluate in Tony's guestimate... worked
Thanks
Title: Re: Excel Date Conversion - revisted
Post by: stanl on July 30, 2013, 02:05:58 AM
as a P.S. - I knew I had come across Evaluate before; it was 2004 and the code involved Excel's SumProduct().