1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Brad
  • 1,450
  • 2
  • 16
  • 37
  • Can't you scrap the `QueryTable` and re-generate it every time? – Mathieu Guindon Dec 20 '16 at 21:23
  • *Microsoft SQL database* - which one are you referring to?? Microsoft **SQL Server** - the RDBMS system from Microsoft - or MS Access with SQL as its query language. Not quite clear - please add a relevant tag - either `sql-server` or `ms-access` to clarify – marc_s Dec 20 '16 at 21:48
  • The order of the fields in your query determines the order of the output table. Why don't you just change the query to output in the order that you want? – nbayly Dec 20 '16 at 22:51
  • @marc_s -- I believe he's using MS Query inside of Excel. I added that as a tag. – Hambone Dec 21 '16 at 04:10
  • @marc_s I mean Microsoft SQL Server. I have updated it in my post. Thanks. – Brad Dec 21 '16 at 13:14

1 Answers1

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.

Hambone
  • 15,600
  • 8
  • 46
  • 69
  • It seems solutions must be pretty limited haha. I greatly appreciate your answer as this has helped me resolve my issue. I ended up doing "SELECT top 0 NULL;" then a refresh, before each official select call. – Brad Dec 22 '16 at 13:42