Copying cell contents from one Excel file to another

[sirkerry]

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?

[PsaltyDS]

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)

Edited July 28, 2010 by PsaltyDS

[sirkerry]

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)

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.

[PsaltyDS]

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.

[sirkerry]

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.

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?

[sirkerry]

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.