Help _Excel_RangeRead

[232showtime]

 

@water,

Need help for getting the date from Excel to Array, Im getting different number instead of date.

Test.xlsx is attached.
 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\user\Desktop\New folder\Test.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook C:\Users\user\Desktop\New folder\Test.xlsx" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Read the formulas of a cell range on sheet 2 of the specified workbook
; *****************************************************************************
Local $aResult = _Excel_RangeRead($oWorkbook, 1, "A1:C5", 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Data successfully read." & @CRLF & "Please click 'OK' to display the formulas of cells A1:C5 of sheet 1.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 2 - Cells A1:C5 of sheet 1")

 

Test.xlsx

[water]

What you see in Excel is the FORMATTED internal representation of the date/time. When you read the value you get the UNFORMATTED numeric value.
The wiki describes this in detail:
"Excel stores date and time as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt .
This is called serial date-time."

[water]

BTW: Can you post an example of the value you get?

[232showtime]

is there any other way to get the proper format of date from excel to array? dont know where to start.

 

42567|b|c
42567|b|c
42567|b|c
42567|b|c
42567|b|c ;this is the date I got from excel = 42567, but in excel 
;format Value is = 16-07-2016 displayed text is = 16-Jul

 

[l3ill]

Cant you use the $iReturn  parameter and use 3 to return:

3 - The displayed text

???

[232showtime]

I tried but, I got error for $iReturn - 3

[water]

@errot = 3 stands for "invalid range". The example in the OP is correct, did you change it?

[l3ill]

Yup...added this to the end of your above code

Local $aResult2 = _Excel_RangeRead($oWorkbook, 1, "A1", 3)
MsgBox(0,"", $aResult2)

and get: "16-Jul"

 

 

 

[232showtime]

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\user\Desktop\New folder\Test.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; *****************************************************************************
; Read the formulas of a cell range on sheet 2 of the specified workbook
; *****************************************************************************
Local $aResult = _Excel_RangeRead($oWorkbook, 1, "A1:C5", 3) ;---------changed to #$iReturn = 3 Error, I always got error If I use $iReturn 3
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Data successfully read." & @CRLF & "Please click 'OK' to display the formulas of cells A1:C5 of sheet 1.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 2 - Cells A1:C5 of sheet 1")

yes I changed it because I want to read all the cell from excel to array other values of $iReturn is working fine but if I use 3 I got error. I attached also the excel file for the example script...

@l3ill,

try range A1:C5, $iReturn 3 you'll get error.

 

Edited July 16, 2016 by 232showtime

[water]

Option 3 only works for a singl cell.

Try option 1 and I think you will get something like this 20160716165358

[AutoBert]

11 hours ago, water said:

"Excel stores date and time as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt .
This is called serial date-time."

this should do the job:

#include <Date.au3>

$nExelres=42567.25
ConsoleWrite(_convertSerialDate($nExelres)&@CRLF)

Func _convertSerialDate($nDT)
    Local Const $dtEXcel='1899/12/31 00:00:00'
    Local $iDate=Int($nDT)-1 ;adjusted after reading wiki and why result is 1 day to much: http://www.cpearson.com/excel/datetime.htm
    Local $iTime=Mod($nDT,1)
    $iTime=int(24*3600*$iTime)
    $dtRes=_DateAdd('D',$iDate,$dtEXcel)
    $dtRes=_DateAdd('s',$iTime,$dtRes)
    Return $dtRes
EndFunc

As i added 0.25 the time is 06:00 AM.

Edited July 16, 2016 by AutoBert

[232showtime]

many thanks everyone,

@AutoBert very nice function.

whats with the .25??? I got 42567 values for date only...

 

Edited July 17, 2016 by 232showtime

[AutoBert]

[water]

As this has been asked a few times I will add the function to the wiki

[AutoBert]

@water it's only 2 min work to skript the func. After 5 min "why 1 day to much?",  i read your wiki and followed the link, so now i know the known bug (leapyear 1900) is a feature in Excel to be compatible with Lotus 123 (they where first and have the bug also) so M$ must implement a func with this bug.

But inserting in wiki is a good idea (100 User * 2 min = 200 min [Normalcase?]) are 200 min wasted time, a better idea is changing _Excel_RangeRead to returning also the displayed value when range is a array.

I added errorhandling:

#include <Date.au3>

$nExelres=42567.25
ConsoleWrite(_convertSerialDate($nExelres)&@TAB&@error&@TAB&@extended&@CRLF)
$nExelres='42567.25'
ConsoleWrite(_convertSerialDate($nExelres)&@TAB&@error&@TAB&@extended&@CRLF)
$nExelres=-305670.25
ConsoleWrite(_convertSerialDate($nExelres)&@TAB&@error&@TAB&@extended&@CRLF)
$nExelres=-425670.25
ConsoleWrite(_convertSerialDate($nExelres)&@TAB&@error&@TAB&@extended&@CRLF)

Func _convertSerialDate($nDT)
    Local Const $dtEXcel='1899/12/31 00:00:00'
    Local $iDate=Int($nDT)-1 ;adjusted after reading wiki and why result is 1 day to much: http://www.cpearson.com/excel/datetime.htm
    Local $iTime=Int(Mod($nDT,1)*24*3600)
    $dtRes=_DateAdd('D',$iDate,$dtEXcel)
    if @error Then return SetError(@error, @extended, $dtRes)
    $dtRes=_DateAdd('s',$iTime,$dtRes)
    return SetError(@error, @extended, $dtRes)
EndFunc

btw. the Help is wrong (_DateAdd):

Quote

Remarks

Valid initial date must be between "2000/01/01 00:00:00". and "3000/12/31 23:59:59".
 

and the initial date is before 2000/01/01 and result date can be much before.

Edited July 17, 2016 by AutoBert