Change Column location of SQL statement in Excel

Question

Within excel I have connected my Microsoft SQL Server database to it, to display results. The system I have set in place is built off of a form. If a user chooses option 1, the query results will show:

Select person, car, house from mytable1

If the user chooses option 2, the query result will show:

Select job, person, land, truck from mytable2

The very first select statement will give me a table in the column order in which I would like it. However, as a user uses the form again, it will re-query it to use which ever select statement is requested. When the re-query happens the column order which items are shown are in different areas. Even if the select statment is stated within the same order. Is there a way in which I could order the columns in a specific order?

I've attempted to unchecked "Preserve Column Sort" within the Data Range Properties, but ends up leaving empty columns. I.E. : Column1, Column2, Column3, etc.

Answer 1

You may already know this, but since Excel allows you to move the columns in a table / ListObject around, it seeks to preserve any changes you make. So, if you run a query:

select one, two, three

And then move the column "three" in front of "one," when you re-run your query, it will keep them in that order in the ListObject, even though the query said otherwise.

This also means if you add a column, no matter where you add it, it will go to the end when MS Query renders the output.

select four, one, two three

("four" goes to the end in Excel, even though it was listed first in SQL)

In your example, the column "person" was common across the two queries, so Excel (MS Query) would move it to the first position and put all other columns after that.

When Excel deletes the old columns, it leaves a tracer behind -- you may notice the columns that follow your table aren't the normal size; they are the size of the fields that were deleted. I call them "ghosts."

This is a serious hack, but the only way I know of to alleviate this problem is to run a bogus query (ie select 1), delete the ghosts -- remove the entire columns, and then run your second query. Here is some ugly code I use in VBA to do this:

Dim lo As ListObject
Set lo = Sheets("Sheet1").ListObjects("Table_ExternalData_1")

Range(lo.HeaderRowRange.Offset(0, lo.HeaderRowRange.Columns.Count), _
  Range("XFD1")).EntireColumn.Delete

Yes, this deletes every column after the table, which means if there is useful data above or below the table in columns after the table, those are wiped out.

Maybe there is a better way -- I'm curious to see if you get any other solutions.

Answer 2

Check your Data Range Properties. Uncheck "Preserve column sort..." or all. And Replace all cells, clear empty cells