232showtime

Help _Excel_RangeRead

15 posts in this topic

 

@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


ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites



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."


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

 


ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites

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


ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

#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 by 232showtime

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites

Option 3 only works for a singl cell.

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by AutoBert
1 person likes this

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

many thanks everyone,

@AutoBert very nice function.

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

 

Edited by 232showtime

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites

:)

Share this post


Link to post
Share on other sites

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

@water it's only 2 min work to skript the func. After 5 min :sweating: "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 by AutoBert

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now