Excel workbook connection makes file size large

Question

I have a series of 30 Excel reports ( .xlsm ), each of which has a unique connection configured for the database. A join is just a short SQL script that captures the data that will be displayed in the report. Data is filled in 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 update the connection.

Each so often, we need to send databases of 30 reports to other working groups so that they can manually update files independently. This can be frustrating because some of the reports are very large (30mb +). This makes sending emails difficult, and downloading them / downloading them several times a day is just a hassle.

To mitigate this, before distributing report templates, I try to delete all rows in the tables and any unused range. This helped, but there are a few more files that are very large (30 MB +), although we deleted everything in the book, except for the join, and an empty table.

Through tests, I realized that if I delete the configured connection, the file size will become small enough (<1mb), as I expected. This makes me think that Excel connections have a kind of cache that needs to be cleared, however I cannot find any links for this.

Does anyone know a simple way to reduce the size of a connection so that I can programmatically use VBA / Powershell?

Answer 1

If deleting a configured connection reduces the size of your file, you can write a macro to delete your connections, and another to restore them. As suggested by Noldor130884, you can automatically run macros on Workbook_Open and Workbook_Close .

Answer 2

Office Online - Create, edit, and manage external data connections

The above link seems to make the following expression:
"Deleting a connection only removes the connection and does not delete any object or data from the workbook."

Answer 3

It seems to me that the problem is with formatting. I don't know why, but in my files, excel reformatted all the rows and columns when adding a form with data from the connection. So the sheet was very large, but if you check the xml file, it will only display formatting data. As soon as I manually delete all the "empty" lines, the file size will be normal again. Hope this helps, helped in my case.