Excel Workbook Connection Makes File Size Large

Question

I have a series of about 30 Excel reports (.xlsm), which each have a unique connection configured to a database. The connection is just a short SQL script which grabs the data to be shown on the report. The data is populated into a table (not a pivot table).

Every week we need to update these reports, so I use a simple PowerShell script to open each of the files and refresh the connection.

Every so often we need to send the base 30 reports to other work groups so they can manually update the files on their own. This can be a nuisance because some of the reports are very large (30mb+). This makes emailing difficult, and uploading them/downloading them several times a day is just a hassle.

To mitigate this, before we distribute the report templates I try to delete all the rows in the tables, and any unused range. This has helped, but there's still several files that are VERY large (30mb+) even though we've deleted everything in the workbook except the connection, and the empty table.

Through tests, I've realized that if I delete the configured connection, the file size becomes sufficiently small (<1mb) which is what I would expect. This leads me to believe that Excel connections have a sort of cache that needs to be cleared, however I can't find any references for this.

Does anyone know a simple way for reducing the size of a connection in such a way that I could do so programmatically using VBA/Powershell?

Answer 1

If deleting the configured connection reduces your file size enough, you could write a macro to delete your connections and another to reestablish them. As Noldor130884 suggested, you can automatically execute the macros on Workbook_Open and Workbook_Close.