Sign in to follow this  
Followers 0
sirkerry

Copying cell contents from one Excel file to another

6 posts in this topic

I'm trying to copy all of the cell contents (except for the blank cells) of one Excel .XLS file (that contains unformated data but placed in matching cell location it would be if it was in the Excel file I'm trying to copy the data to) to a formated 'template' Excel .XLS file. I've found that $oExcelData.Worksheets("Sheet1").Range("A1:IV65536").Copy and $oExcelTemplate.Worksheets("Sheet1").Range("A1").PasteSpecial(0xFFFFEFBD,Default,True,Default) work for copying all of the data between the two worksheets, but the blank cells are also getting copied which is overwriting the cells on the template with blank cells. Any suggestions how to fix this?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Limit your source range to used cells, not just "A1:IV65536".

You can get the used range like this (lifted from _ExcelReadSheetToArray):

; Get size of current sheet as R1C1 string
    ;     Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
    Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)

:blink:

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Limit your source range to used cells, not just "A1:IV65536".

You can get the used range like this (lifted from _ExcelReadSheetToArray):

; Get size of current sheet as R1C1 string
    ;     Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
    Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)

:blink:

Okay, thanks, I figured that was a way to do that, but I'm more interested in getting the blank cells in the used range not to be copied over.

Share this post


Link to post
Share on other sites

Ah, that requires either much more subtlety than I have in Excel, or a brute force approach to just copy through one cell at a time. For the later, read the source with _ExcelReadSheetToArray() then walk through the array with a nested loop to hit all the cells, skipping the blanks, and copying to the destination one cell at a time.

:blink:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Ah, that requires either much more subtlety than I have in Excel, or a brute force approach to just copy through one cell at a time. For the later, read the source with _ExcelReadSheetToArray() then walk through the array with a nested loop to hit all the cells, skipping the blanks, and copying to the destination one cell at a time.

:blink:

Ah, I looked at using the _ExcelReadSheetToArray() option first, but it's doing something screwy to any dates contained in the cells it reads. Is there a way to get it to not mangle the dates?

Share this post


Link to post
Share on other sites

Ah, I looked at using the _ExcelReadSheetToArray() option first, but it's doing something screwy to any dates contained in the cells it reads. Is there a way to get it to not mangle the dates?

Found an answer to my Excel date problem here.

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