Sign in to follow this  
Followers 0
yodasoja

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

5 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

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.

1 person likes this

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

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.

Share this post


Link to post
Share on other sites

If you want to copy all used cells you could use

$oExcel.ActiveSheet.UsedRange
1 person likes this

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

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  
Followers 0