Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
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 '12 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 '12 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. –  Graphth Jul 27 '12 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. –  Graphth Jul 27 '12 at 19:29
add comment

1 Answer

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.

share|improve this answer
    
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. –  Graphth Jul 27 '12 at 19:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.