Jump to content

Recommended Posts

Posted

 

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

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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.

Posted

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.

Posted

@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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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.

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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

Posted

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...