sirkerry 0 Posted July 28, 2010 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
PsaltyDS 39 Posted July 28, 2010 (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) Edited July 28, 2010 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
sirkerry 0 Posted July 28, 2010 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. Share this post Link to post Share on other sites
PsaltyDS 39 Posted July 28, 2010 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. 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
sirkerry 0 Posted July 29, 2010 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? Share this post Link to post Share on other sites
sirkerry 0 Posted July 29, 2010 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
