_ExcelReadtoArray reading oddly. Any way to force it to copy literal Strings instead?

[yodasoja]

Hello.

I am copying a page from one Excel file to another. I've decided to use _ExcelReadToArray and _ExcelWriteFromArray to get the large, dynamic page's data from one file to the other. The only problem with this is that the _ExcelReadToArray has a problem with dates in the format MM/DD/YYYY. Instead of 11/10/2013, the array contains "2013111000000" (yes, with all those zeroes) after the array is filled by the call. Then, when I write the array to the other file, it evaluates the "/" as a divides and I get a similar number with an "E-8" on it.
Is there any way to make the reading and writing assume that they are just pure strings and not have it (or excel) evaluate the contents?

Thanks ahead of time!




PS here is some sample code from the section with the reading/writing:

$rcaTATreport = _ExcelBookOpen($RCAfolder & $sTATreport)
$macroSheet = _ExcelBookOpen($pathToCurrentWeek & $sMacroSheet)

;copy the "RCA lists" tab to an array
$rcaTATreport.Sheets("RCA List").Select
Local $aArray = _ExcelReadSheetToArray($rcaTATreport, 1) ;Starts at first row for headers

;Display the value of R2C14 aka N2, to see if contents are being read as strings or evaluated
MsgBox(0, "", "Contents of Array at N2: " & ($aArray[2][14]) )


;Delete "RCA List" from macroSheet, and then create a new (blank) one
_ExcelSheetDelete($macroSheet, "RCA List") ;Delete Sheet by string name of SheetName
_ExcelSheetAddNew($macroSheet, "RCA List")  ;Creates a new sheet with name "RCA List"

;Select "RCA List and then copy the contents of the array to it.
$macroSheet.Sheets("RCA List").Select
_ExcelWriteSheetFromArray($macroSheet, $aArray) 

EDIT:

I used user Water's Excel rewrite and here is the code that worked for me, in case anyone in the future is curious:

Global $oExcel = _Excel_Open()
$rcaTATreport = _Excel_BookOpen($oExcel, ($RCAfolder & $sTATreport))
$macroSheet = _Excel_BookOpen($oExcel, ($pathToCurrentWeek & $sMacroSheet))


$rcaTATreport.parent.Sheets("RCA List").Select  ;Select RCA List page for _Excel_RangeCopyPaste function
$macroSheet.Sheets("RCA List").Select           ;Select RCA List page for _Excel_RangeCopyPaste function
_Excel_RangeDelete($macroSheet.ActiveSheet, $macroSheet.ActiveSheet.UsedRange)  ;(book.sheet, range)

_Excel_RangeCopyPaste($rcaTATreport.ActiveSheet, $rcaTATreport.ActiveSheet.UsedRange, Default)  ;copies dynamic range from workbook1 to the clipboard
_Excel_RangeCopyPaste($macroSheet.ActiveSheet, Default, "1:1")          ;Copies from clipboard with topleft at 1:1

Edited December 19, 2013 by yodasoja

[jdelaney]

Can format after the fact...excel saves datetimes like you have copied in:

$string = "2013111000000"
$new = StringRegExpReplace("2013111000000", "(\d{4,4})(\d{2,2})(\d{2,2})(.*)","$2\/$3\/$1")
ConsoleWrite($new & @CRLF)

11/10/2013

or, if you enter the data into excel like this, it wont format it:

'11/10/2013

notice the apostrophe

Edited November 20, 2013 by jdelaney

[water]

You could try my rewrite of the Excel UDF.

At the moment you only copy the data.

With function _Excel_RangeCopyPaste from the rewrite you can copy a range directly from one sheet/workbook to another.

[yodasoja]

Water, how would I copy the data if it is a dynamic range each time? From the _Excel_RangeCopyPaste function, it looks like you can only copy if you know the exact R#C# you need.

[water]

If you want to copy all used cells you could use

$oExcel.ActiveSheet.UsedRange