Jump to content

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


Go to solution Solved by water,

Recommended Posts

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

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

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • 2 weeks later...

If you want to copy all used cells you could use

$oExcel.ActiveSheet.UsedRange

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...