Running SQL and Excel faster into Pivot/Sheet (from multiple sheets).

[countryfan_nt]

Hello friends, hope all is well!

I learned a very cool trick! https://www.contextures.com/PowerPivot-Identical-Excel-Files.html

Now I can use PowerPivot to have mutli sheets/files with Identical Structure in one sheet/pivot (more than 5 million rows).

the problem is that the code is too slow! yes I know I am piling 5-6 million rows.

How can I speed things up, is there anything we can edit from the scripts below?

seriously thank you so much! in advance.

Connection String:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=F:\DATABASES\AvgClaimCo\17-18.xlsb;Mode=Share Deny Write;Extended Properties="HDR=YES;";Persist Security Info=False;Jet OLEDB:System database=;Jet OLEDB:Registry Path=;Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

SQL statement:

SELECT ['JAN-APR$'].*   FROM ['JAN-APR$']

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\17-18.xlsb`.[MAY-AUG$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\17-18.xlsb`.[SEP-DEC$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\16.xlsb`.[JAN-MAR$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\16.xlsb`.[APR-SEP$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\16.xlsb`.[OCT-DEC$]

 
UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Jan-Mar$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Apr-Jun$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Jul-Sep$]

UNION ALL
SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Oct-Dec$]