I'm in the latest version of Excel 365, and have a data connection set up to a php page on my website. It works flawlessly, but every refresh makes the columns default back to being just wide enough to fit the source data.
BELOW the data-connected table, I have other formulas/values. These are longer strings, and as such they end up as "#####" when the column sizes get adjusted to match the incoming data.
How can I fix this?
Thanks!
13 Answers
It seems that the answer by user @Sach didn't work. It might be improved by, in addition to clearing "Adjust Column Width", to set in the properties the item "Preserve Column Sort/Filter/Layout".
If that does not help, a solution that will work is to use an intermediate hidden worksheet that will contain the data from the external data connection, and link your worksheet to that, instead of directly to the external data source.
The reason for the problem is that Excel actually stores formatting data in a cache with the retrieved data. When the data is refreshed, Excel invalidates this cache, so that the formatting is changed together with the data
0"Anita Oakley at Microsoft e-mailed me this morning and says to right-click on the table-->External Data Properties-->Uncheck the "Adjust column width" check box."
It worked for me (Excel 2019).
Go to DATA tab -> Connections. In the dialog box click "Click here to see where the selected connections are used". It will show the table in Excel that is being used by the connection. Click that table and Close the dialog box. Next, in DATA tab select Properties and in the dialog box uncheck "Adjust Column Width". Click OK. Now Refresh All and you will see that column width has not changed and all your ##### are replaced with the values.
Sach
