1

Is there a way to improve performance of importing big chunks of .xls data to a main .xls?

My code to import data:

Applications at start (and reversing at the end):

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
SaveChanges = False

Import code:

Workbooks.OpenXML (filePath & "chunkOfData001.xls")
Set WBtemp = ActiveWorkbook
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
WBmain.Activate
Sheets("ImportedChunkOfData").Activate
Range("A1").Select
ActiveSheet.Paste
WBtemp.Close

I followed user GWD's tip and got a really great result.

Here is the code now. Can it be pushed further?

I kept Applications

Import code (relevant lines):

Dim WBmontagem As Workbook
Dim WBsap As Workbook
Dim v As Variant

Set WBmain = ActiveWorkbook

Set WBtemp = Workbooks.OpenXML(filePath & "chunkOfData.xls")
v = WBtemp.ActiveSheet.Range("A1:I150000").Value
WBmain.Sheets("ImportedChunkOfData").Range("A1").Value = v
WBtemp.Close
Community
  • 1
  • 1
William Cezar
  • 49
  • 5
  • 1
    This might help you: [How to avoid using Select in Excel VBA](https://stackoverflow.com/a/10717999/12287457). – GWD Nov 23 '22 at 16:28
  • you really helped me. but i wonder if i can push harder – William Cezar Nov 23 '22 at 19:16
  • 2
    That's likely about as fast as it can go, unless you can get some speedup by (eg) moving files to a faster drive, since opening the file is likely the slowest part of the process. – Tim Williams Nov 23 '22 at 23:44

0 Answers0