Jump to content
Sign in to follow this  
DanielTyrkiel

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

Recommended Posts

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 :D)

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

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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...

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
Sign in to follow this  

×