donderdag, januari 08, 2009
Adding a field in Excel from Access
You have this Access mdb database, and an Excel xls spreadsheet. You access data from the spreadsheet, and linked from Excel to Access (maybe using the Excel Data import query Wizard). But than you add a field in Access, and want it to show up in your Excel. So you right click on the sheet in Excel, try to edit the query, but you get "This query cannot be edited by the Query Wizard". How to solve this?
Well: continu editing the query (ignore the message). In Microsoft Query drag the fields from the list of fields to the lower part of the screen (where the result of the query is shown). Make sure the lower part of the screen is filled with some data. Now click "Return data" (the icon of a door with an arrow pointing at it). Now the field shows up in your Excel sheet... (if you exit the Microsoft Query in another way, you're asked whether and where you want to save the query as a seperate file: i didn't want that, i wanted Excel xls to remember the changes i made).
Not very intuitive, and maybe there is a better way, but i spent to much time too many times and decided to document this, for myself and maybe someone else ;-).
(Image taken from http://www.dicks-blog.com ).