Question

2
Replies
117
Views
 Jessica Visocchi (JessicaV8159)
NA

NA
US
JessicaV8159 Member since 2019 7 posts
NA
Posted: November 5, 2019
Last activity: November 6, 2019
Posted: 5 Nov 2019 15:51 EST
Last activity: 6 Nov 2019 11:48 EST
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.

Robotic Process Automation
Moderation Team has archived post, This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Question, please write a new Question.
Posted: 1 year ago
Posted: 6 Nov 2019 11:38 EST
Konstantin Serditov (Konstantin Serditov)
PEGA

Pegasystems Inc.

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.