Unanswered: Exporting part of an Access table to Excel
Hello to whomever may help:
I am currently working on trying to get a table exported into an Excel document. However, here's the twist:
I have an Access table, for example, let's call it "Apples." Inside the table is a field called "Description." That field holds the description of what I'm trying to import. I also have an "ID" field that is set as the primary key. It's simply a identification number for each description that is numbered straight down the list as 1, 2, 3, 4, 5, 6, etc. What I would like to do is import the Description field into Excel by only entering the ID number. This may take some VBA code, but so far I haven't used any at all in the spreadsheet. I have used about 4 embedded IF statements and moderately complex formulas. Are you aware of any way to accomplish the desired outcome? I would like to get to the point where the ID number would be the only thing that would have to be entered and the description would automatically fill in after the ID number is entered into the spreadsheet. Any ideas would be greatly appreciated! Thanks much!
Unless I am missing something in your question the solution is fairly simple.
In excel enter an ID number into the cell you want to use. Then select the field you want the description to be inserted and go to Tools - Get External data and make a new query.
When writing the query just use [enter ID number] as the criteria for you ID field. Run the query, get back into excel right click on the cell with the result and go to Parameters. You can then select the cell with the ID number in it to be the data used for the [enter ID number] criteria and also set it to update automatically.
I got a query to work, but the formatting of the cells gets all messed up. I tend to believe that there is a way to make the query without messing things up. For example, the font changes from what I had set it, the cells expand (I had the wrap text option on, but then it was overwritten by the query in some way--hence, the cells expanding to fit the whole requirement), and seems to just insert the columns instead of putting the description into the existing column. The goal is to make this as user friendly as possible because this is something that is going to be used multiple times in the future by multiple people. I would like to provide accurate documentation for this as well and would really like to know exactly what is going on in the spreadsheet at all times.
Is there any way that you would be able to send me step-by-step instructions on how to do this. Please keep in mind that I am using Excel 97 and Access 97. And yes, I do agree with you that the information should be able to update automatically because that would be much nicer than an alternate, delayed route. However, I didn't even see an option for Parameters when I right clicked the cell in both Access or Excel (we may have different versions of each), so I wasn't sure what you meant by that.
It seems like you understand what I am doing, so please respond. I like your help, but I would greatly appreciate you to expand on your previous reply. Thanks for the help and for the help to come! I really appreciate it!
First off: I am using Office2000 so things may be a little different.
Sorry I did get my info to you a little wrong. There are two different options in the data menu you need to use. In Excel2000 they are under the external data item. First is the parameters which gives you the option of setting which cell to use for the criteria in your MSquery. Note there is a check box for update automatically when cell value changes.
The second is Data Range Properties. This has options about preserving formating, sorting, column widths etc. Basically you end up turning most things off except preserving formatting so it just puts the data in and nothing else.
Of course this could be completely wrong for Office97. If so I am sorry I can't be of much more help.
At first, I couldn't find the Data Range Properties area that you had mentioned in a previous post. But now, I did find it. I attached a JPEG image that shows a screen shot of that window. I don't know if that differs from the same area in Office 2000. I would tend to believe it's a little different because I didn't see anything that would update the query automatically. If you see different, I'd be thrilled because that's what I really need. If Excel 97 doesn't have that option, this whole operation probably won't work. Well, take a look at the attachment and let me know. That's currently the biggest issue, so we can start there. Let me know if you see it in there. Thanks!
Only difference between your external data properties window and the Excel2000 window is the ability to turn column width adjustment on and off. Not sure which way Excel97 default.
However I also have a separate window called Parameters, which should be in the same menu as Data Properties (if it exists). I have attached an image for the parameters screen, it is fairly self explanatory once you see it.
If you don't have the parameters option, check the help file first to see if it is hiding somewhere else, and if that fails then perhaps there is a way to actually put the parameter into the query directly.
On that point, remeber that in my MSQuery I have put criteria for one of my fields as [Territory ID] hence it now exists in the parameters dialog window.
Thanks for all your help. I really do appreciate it. If I have any more problems, I will be sure to post them. Hopefully, with the help you've given me, I can try some different things and maybe I can figure out something that does work. If it works, it works. If not, that's ok too. At any rate, thanks again!