[Solved] Excel date to array format issue (not new I'm sure...)

[DanielTyrkiel]

Solved: sorry, didn't read the excel UDF properly

regards

Hi,

have you come across the date format being kept after the cell value had been added to the array?

I DON'T have a problem with the formatting not being kept, because I simply can cut the long string and re-organize it, but when it saves "properly" then my formula for splitting the long string produces a mangled string of characters (obviously)

Here's what I found after running a test script on my big spreadsheet:

https://docs.google.com/open?id=0ByUpE9M4BFXIVTJpclFWWGtPNlU

sorry for the trouble. I have looked through the forum but haven't come across this problem (but I have been called blind before )

Here's the test code I whipped up to produce the array:

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

Global $YearandWeek = "all weeks 2012"
Global $oExcel

$sFilePath002 = @MyDocumentsDir & "\Delivery Planning" & "\" & $YearandWeek & ".xlsx" ;path to open the chase up spreadsheet
$oExcel = _ExcelBookOpen($sFilePath002)

Local $j

;check which row number is the last one
For $j = 1 To 2000
$sAnotherCellValue = _ExcelReadCell($oExcel, $j, 1)
$sAnotherCellValue = StringLen($sAnotherCellValue)
If $sAnotherCellValue = 0 Then ExitLoop
Next ;by the time it's finished $j is row number one below the last filled one

Local $i
Local $avArray[1] ;create the array to work with
Local $date
$avArray[0] = "the list" ;populate the first line
For $i = 2 To $j - 1
$date = _ExcelReadCell($oExcel, $i, 12) ;read the date cell
_ArrayAdd($avArray, $date) ;add date to the array
Next

_ArrayDisplay($avArray, "dates")

Edited October 30, 2012 by DanielTyrkiel

[DanielTyrkiel]

I've just noticed that when I changed the formatting of the entire column to "general" then the ones that show as a date in the array also show as a date in excel.

The others - that show as a long string are presented as a 5 digit string of numbers in excel...

[DanielTyrkiel]

OK,

NOW I have done my homework...

After re-reading the Excel.au3 file...

There is an option in _ExcelReadCell() that you put in after the column number. If you set it to "3" then it reads the text seen by ze humanz...

Sorry to be a dafty...