0

I have a table in an Excel file which has a connection to an Access Query. When I first create the table in Excel, the data comes in with exactly the same order as how I set up the Access query. When I change something in the Access query, for example if I change the name of a field but don't change the data that comes in, in Access that column stays in the same place. But, in Excel, when I refresh the table, that column now comes at the end. I need the columns to be in a certain order and I put them in the correct order in Access but they're not coming in the same order in Excel. And, I need to be able to change the fields in Access from time to time without having to do a ton of work to get the columns back in the correct order in Excel.

Any ideas? Thanks!

Improve this question
4
  • 1
    Can you not build a query/view with a specified field order? It seems that's the source of your trouble. Something like SELECT field1, field2, field3 FROM table rather than SELECT * FROM table?
    – Excellll
    Jul 27, 2012 at 17:37
  • It seems to be that you are hard coding the colums into your excel program. Try using a VLOOKUP for the table column name instead of hard coding the column location
    – wbeard52
    Jul 27, 2012 at 18:08
  • @Excellll Maybe... maybe I could just change the query in Access to a make table query (instead of a select) and connect to that.
    – GeoffDS
    Jul 27, 2012 at 19:18
  • @Excellll Part of the problem is I have around 150 fields, so it's sort of a pain... and I've already built a query in a specified order in Access. Why do I need to do it twice? I checked the SQL View in Access and the SQL there brings in the fields in the order I want, and the Design view shows them in that same order. But, when I import to Excel, if I have made changes, it does not come in that order.
    – GeoffDS
    Jul 27, 2012 at 19:29

1 Answer 1

Reset to default
0

I do not believe there's a solution for this. Excel is not smart enough to know that you changed something on the Access side. There's a fundamental fault in your design if you need to keep changing column names.

Improve this answer
1
  • Well, I'm rehauling the system now so I had to change a few names to get things to work properly. In the future, it would be more like adding a new column, which would actually entail changing a column named Blank (or something like that) to something else. This wouldn't happen often but it might every few weeks.
    – GeoffDS
    Jul 27, 2012 at 19:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .