Software: Workbook to Workbook Copy & Paste

I’m playing around with some stuff in Excel using VBA code behind and I ran into some problems with workbook to workbook copy & pastes being very slow.

Using the technique of:

Open Data workbook
Select data.
Do Selection.Copy
Open Computation workbook.
Do Selection.PasteSpecial
Close Data workbook.
Do calculations

worked fine for the first few files but as the file count got higher and higher it was taking longer and longer to perform the Copy & Paste functionality. Opening Task Manager I found that Excel had generated over 186,000,000 page faults. The memory was fragmenting and the time required to copy/paste for each additional files was going up. Doing a little research I discovered that the Clipboard was involved in my copy.

Further research identified a better way to do the copy:

‘Create reference objects to the Compute workbook
Set oCompBook = Workbooks("ComputeResults.xls")
Set oCompDestSheet = oCompBook.Worksheets("Sheet1")

‘Create reference objects to the Data workbook
Set oDataBook = Application.Workbooks.Open(DataFile.xls)
Set oDataSrcSheet = oDataBook.Worksheets(1)

'Copy the data from the Data workbook to the Compute workbook
range1 = "A2:F56000”
oDataSrcSheet.Range(range1).Copy oCompDestSheet.Range(range1)

‘Clean up
Set oDataSrcSheet = Nothing
oDataBook.Close (False)
Set oDataBook = Nothing

The new algorithm eliminates the Clipboard resulting in a solution to my page fault problem. This significantly sped up my Compute Workbook’s execution time; from several hours to a few minutes.

Technorati tags: Excel, VBA, Copy, Paste, Microsoft