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.
Posted: 1 year ago
This is good to know Konstantin. Thank you for this. I did not know about these properties.
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.