Question
2
Replies
101
Views
NA
Posted: November 5, 2019
Last activity: November 6, 2019
Closed
Excel Import/ExportData Taking Too Long
I have an automation that runs reports from a web application, then exports the output to Excel. From there, I export it to a data table and then import that into a different excel macro-enabled workbook. I am currently using the Export/Import Data methods, and it works... it just takes a very long time. The reports can vary anywhere from 0-26k data entries and up to 30mins for each report to finish. Is there a quicker way to do this? Any help would be appreciated.
Here are two things you can try to speed it up.
1. Disable calculation on the fly while inserting data by setting property of _ExcelApplication object Calculation = xlCalculationManual and then calculating everything after completion of insert.
2. Disable ScreenUpdating while inserting data in the similar way - _ExcelApplication object has property ScreenUpdating which should be set to False before insert and then back to True after insert.