Import a time from Excel

[Ghost24de]

Hi,

i will import a time from a Excel Cell.

First problem is:

like this
XLGetCell>xlBook,InputVar,1,9,InputTime

The value of this cell ist 22:00:00 (hs european time)

But, after the import .... Macroshedu.. lose the format and so i recive the value inside the variable 0.916666666666667.

How can i do, that i have not 0.916666666666667?
I need this format hs



Second one is,
if i close with
XLQuit>xlBook

but the Excel (process) is open and will not be close!
I only can close the process with the killproce... command... but i think thats not the fine and right way


great thanks for your help

[jpuziano]

Hi Ghost24de,

Here is some early feedback on what "may be" happening there and an idea on how you could convert 0.916666666666667 back to 22:00:00

I notice that...
22/24 = 0.91666666666666666666666666666667

So...
1/24 = 1 hour = 0.041666666666666666666666666666667

- in a loop, count how many times you can subtract 0.0416 from total, that is hours value

(1/60)/24 = 1 min = 0.00069444444444444444444444444444444

- in a loop, count how many times you can subtract 0.000694 from remainder above, that is min value

((1/60)/60)/24 = 1 sec = 0.000011574074074074074074074074074074

- in a loop, count how many times you can subtract 0.0000115740 from remainder above, that is sec value

- now that you have hr, min and sec values, build a string using those values separated by colons :

And now someone will post an easier way... take it away...

[Ghost24de]

mhhh
is ther noone ... with a idea from a easier way?

mhh i think .. i will splitt the excel cell into 3 single cells.. so one for the hour, one for the min and one for the sec... so i must import 3 values, but i think it is easier to go this way to write a loop and counter inside the makro.

And for the second answer... with the close porblem from excel?

[Marcus Tettmar]

When you use XLGetCell it always will return the VALUE. The formatting is handled by Excel. You are seeing the raw data.

What you can do is use VBScript's FormatDateTime function to format the time the way you want it:

VBEval>FormatDateTime(0.916666666666667,3),formattedTime
MessageModal>formattedTime

So you could use this with XLGetCell:

XLGetCell>xlBook,InputVar,1,9,InputTime
VBEval>FormatDateTime(%InputTime%,3),formattedTime
MessageModal>formattedTime

[hagchr]

Hi,

I get an error code if excel is formatted with one of the excel date formats. Unformatted it works well, so format seems to impact (I am using excel 2010)

Microsoft VBScript Compilation error :1006.

[Marcus Tettmar]

This works for me:

XLOpen>%USERDOCUMENTS_DIR%\book1time.xlsx,1,xlH
XLGetCell>xlH,Sheet1,1,1,theTime
VBEval>FormatDateTime(%theTime%,3),result

I tried it with cell A1 formatted to Time, of Type: hh:mm:ss like so:



If it isn't working for you can you share what format you are using.

[hagchr]

I was first playing with pulling also the date information and still had the date format left on (mm/dd/yy). (Admit maybe inconsistent with looking for the time but I did not think the format would affect it)