seres

excel range read date problem

4 posts in this topic

hello, im having trouble reading dates from excel, for example, i have a column with date  07/04/2015 and it reads it like 20150407000000, is there a way to read it as it is, i have tried excel rangeread with option 3 but it returns me nothing

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



Global $Sheet1_ARRAY, $Sheet2_ARRAY, $Master_ARRAY
Local $oExcel = _Excel_Open(0)


$oExcel1 = _Excel_BookOpen($oExcel, @ScriptDir&"\date.xlsx")
$oExcel0 = _Excel_BookOpen($oExcel, @ScriptDir&"\Master.xls")

$Sheet1_ARRAY = _Excel_RangeRead($oExcel1, Default, Default, )


Global $FinalARRAY[9000][500]; is there a better way to set the values for the array?


Local $iRowsSheet1 = UBound($Sheet1_ARRAY, $UBOUND_ROWS) ; Total number of rows filas.
Local $iColsSheet1 = UBound($Sheet1_ARRAY, $UBOUND_COLUMNS) ; Total number of columns.

$inc = 0
For $columna = 0 To $iColsSheet1 - 1 


        if $Sheet1_ARRAY[0][$columna] = "DATE_APLI"  Then

            For $j = 0 To $iRowsSheet1 - 1
                
                $clasvalue = $Sheet1_ARRAY[$j][$columna]
                $FinalARRAY[$inc][$columna] = $clasvalue
                $inc +=1
            Next
            $inc = 0

        EndIf

Next


_Excel_RangeWrite($oExcel0, Default , $FinalARRAY)


_Excel_BookSave($oExcel0)
_Excel_BookClose($oExcel0)
_Excel_BookClose($oExcel1)

 

Share this post


Link to post
Share on other sites



That's a limitation of Excel. You can only retrieve a single cell with option 3. Translate the date to the needed format using a RegExp or the String* functions. 


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

You may simply trim your array like this:

_ArrayTrim($YourDate,6,1)

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

or loop throug array calling this func:

 

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