Change SQL re-arranges Excel columns

[radi8]

I have a workbook that does several database retrievals into a working data sheet. After all of the SQL data is retrieved, I run a series of VB macros against the retrieved data and create several spreadsheets that are distributed to management. The process is fully automated and efficient (for the most part).

The issue I am having is that on one of the SQL statements, I needed to change one of the data elements from one date field to another (column alias changed, but position in the SQL remained the same).

When the data is returned, the changed column is now at the end of the data set, and not where the original column was.

example, original layout
select order_number, order_type,pick_date, qty, ext_cost, uom
from ...
yielded an output like:
order_number, order_type,pick_date, qty, ext_cost, uom

after changing the pick_date to ship_date, the
select order_number, order_type,ship_date, qty, ext_cost, uom
from ...
yield an output like:
order_number, order_type, qty, ext_cost, uom, ship_date

since the order has been shifted tot he left of some of the columns, the macro is no longer working...

Is there a way to force the returned dataset to remain in the order dfined by the SQL?

[Richard Schollar]

Hi

Try deleting the querytable object on your sheet and then recreating it with the new SQL (ie so you are not modifying the existing querytable) - of course this assumes you are using querytables for this. On the sheet in question, go to NameManager (xl2007) or Insert>Name>Define (earlier versions) and delete the named range that will look like "External_Data_Query_1" or something like that. That will remove the querytable. Then go via Data>Import External Data>New Database Query to generate the new data using the new SQL.

Richard

[radi8]

Yep, that did it. Thanks. I was totally lost on how to do this.