Jump to content
232showtime

Help _Excel_RangeRead

Recommended Posts

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


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


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
water

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

 


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

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
water

@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
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 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
water

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
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 by AutoBert
  • Like 1

Share this post


Link to post
Share on other sites
232showtime

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
water

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

  • Like 1

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
AutoBert

@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

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.