kamikaz3 Posted August 15, 2007 Share Posted August 15, 2007 hi in my app im using ExcelCOM_UDF by LocaDorwin to read excel files to my gui. My problem comes when i read lets say cell "E6" wich is formatted with a time format like HH:MM in excel cell "E6" i read there 09:00 but when using _ExcelReadCell($oExcel,"E6" ) it returns me "0.34375" is there a way to "reconvert" it to date or is there a specific function to read this formatted cells wich i didnt found? thanks in advance and sorry if you dont understand my english ^^ Link to comment Share on other sites More sharing options...
GMK Posted August 15, 2007 Share Posted August 15, 2007 Here's what I've come up with:#include <Excel.au3> $oExcel = _ExcelBookNew() ;Open Excel $wintitle = $oExcel.Name & " - " & $oExcel.ActiveWorkbook.Name ;Get window title WinSetState($wintitle, "", @SW_MAXIMIZE) ;Maximize Excel window _ExcelWriteCell ($oExcel, "09:00", 1, 1) ;Write cell $sCellValue = _ExcelReadCell ($oExcel, "A1") ;Read cell value $sTime = _NumToTime($sCellValue) ;Convert number to time in AM/PM format MsgBox(0,"",$sTime) ;Show time $sTime = _NumToTime($sCellValue, False) ;Convert number to time in 24-hour format MsgBox(0,"",$sTime) ;Show time Func _NumToTime($sNumValue, $AMPM_Format = True) $sHour = Int($sNumValue * 24) ;$sNumValue is a fraction of a 24-hour period $sRemainder = ($sNumValue * 24) - $sHour ;$sRemainder is a fraction of a minute $sMinute = Int($sRemainder * 60) ;Calculate minute If $AMPM_Format Then If $sHour < 12 Then $sAMPM = "AM" ;Anything before 12:00 is AM Else $sAMPM = "PM" ;Anything after 12:00 is PM EndIf If $sHour = 0 Or $sHour > 12 Then $sHour = Abs($sHour - 12) ;Use Abs in case $sHour is 0 Else If $sHour < 10 Then $sHour = "0" & $sHour ;Add 0 placeholder to hour $sAMPM = "" ;AM/PM is blank EndIf If $sMinute < 10 Then $sMinute = "0" & $sMinute ;Add 0 placeholder to minute Return StringStripWS($sHour & ":" & $sMinute & " " & $sAMPM, 2) ;Return time value and strip spaces at end if format is 24-hour EndFunc Link to comment Share on other sites More sharing options...
Vardebedian Posted August 16, 2007 Share Posted August 16, 2007 hi in my app im using ExcelCOM_UDF by LocaDorwin to read excel files to my gui.My problem comes when i read lets say cell "E6" wich is formatted with a time format like HH:MMin excel cell "E6" i read there 09:00 but when using _ExcelReadCell($oExcel,"E6" ) it returns me "0.34375"is there a way to "reconvert" it to date or is there a specific function to read this formatted cells wich i didnt found?thanks in advance and sorry if you dont understand my english ^^You may use _TicksToTime() function, too:CODE#include <Date.au3>CONST $CONV_DAY_TO_MSEC = 24*1000*60*60local $Hour = 0local $Mins = 0local $Secs = 0 local $ExcelInputValue = 0.34375$Time = _TicksToTime($ExcelInputValue*$CONV_DAY_TO_MSEC, $Hour, $Mins, $Secs )$Time = StringFormat("%02i:%02i", $Hour, $Mins)consolewrite( $Time & @CRLF)Bye. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now