Adding a column to an existing external data query may not give you the results you want. There’s a property of the external data query that freezes the layout. Here’s how it works: Start with a query. This one is from the Northwind database.
Now, edit the query and add a column. Reposition the column so that it’s the second column.
Now the query looks like this
The newly added column shows last, even though we explicitly placed it second. To fix this problem, click the Data Range Properties button on the External Data Toolbar. On the External Data Range Properties dialog, uncheck the Preserve Column Sort/Filter/Layout check box.
Now refresh, and your columns are ordered how you specified.
In VBA, use the PreserveColumnInfo property of the QueryTable object.