PDA

View Full Version : Excel crashes during VBA cell copy



morsoe
04-30-2015, 05:42 AM
Hi,

I am using Cells.Copy to populate a sheet with data from a newly opened source sheet. This method is convenient (as opposed to Sheets.Copy), because the target project contain formulas referring to the specific target sheet.
The source file is an Excel output from SAP/SRM system. With two identical versions of the same source file, excel will crash during cells.copy on the first version but carry out the method without crashing on the second.
This pattern seem to be consistent with observations of the Excel service pack update SP2 = version 14.0.7015.1000. (Previous SP Version 14.0.60291000 does not crash during this process).
The contents of both sheets are identical. One curious difference however, is that .SpecialCells(xlCellTypeLastCell) on the file crashing is AZ324 but IO324 on the file not causing the crash. There is no apparent reason for this difference. The source file can be significantly larger, however this as not influence.
The VBA causing this crash is
WS1.Cells.Copy
WSasapio.Paste
Both of which have been set as worksheets using set statement.

Best regards
Morten

mancubus
04-30-2015, 06:07 AM
what happens when you replace Cells with UsedRange in the code?

Paul_Hossler
04-30-2015, 07:05 AM
The source file is an Excel output from SAP/SRM system.

On my SAP system, what they call an Excel file is really a MIME wrapper around an XML file (IIRC)

Try this:

1. Open the SAP 'Excel' file and do a FileSaveAs with a new name and FileType=XLSX

2. Compare the file sizes. The SAP file would typically be 10x the 'real' Excel file

3. Try to copy the Cells from the FileSaveAs version and see.

BTW, mancubus is right on with only copying what you really need to copy instead of millions of blank cells

mancubus
04-30-2015, 08:00 AM
+1 for Paul..

and going on a diet will be fine if the files' sizes are too large:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=83