I hope someone can help a newbie to Excel with this one.

I have created a spreadsheet from an Access database using an export feature. The spreadsheet is basically a dump from an Access Query, what I need is a way to pull over that information into a prepared Excel Spreadsheet into specific cells on the spreadsheet.

How can I do this????

[I tried to have Access do this directly into the spreadsheet but it was quite involved. It was easier to have Access create or dump the table/query to Excel and then figure how to have Excel move the data to the target spreadsheet]

[I tried to have Access do this directly into the spreadsheet but it was quite involved. It was easier to have Access create or dump the table/query to Excel and then figure how to have Excel move the data to the target spreadsheet]

Try the Import External Data Tool in Excel. From the Data Menu Select 'Data->Import External Data->New Database Query...' This will open a wizard to step through the process of selecting a database, selecting the table, Fields to include and configuring the query. You can select where to put the data on the active worksheet. If you want to place data in none adjacent columns/rows you will need to import data by multiple queries or import to a sheet then use formulas on another sheet to link to the data.

Once you have a data import configured select a cell in the data range then use Data Range Properties on the Data menu or toolbar to set specific conditions for refreshing and displaying the data.

With the Excel Data Import feature you should be able to manage your data conection and import entirely within Excel.


/

Thanks, I tried that and it works fine, but are you saying that I will need to create a seperate query for EACH cell I want to move?

I had no problem copying everything over in one query, but of course it imported it all in one area and not specific cells.

I have about 30 cells needing to copy...

Hi databasemon,

There might be another way to achieve your goal. What I have done in the past was to Import the data from Access into a Dump like table. Then, from the table where you want certain fields that you dumped...just Link those specific areas to those areas on the dump table. using VLookup. OR Copy Each field one at a time and do Edit/PasteSpecial and then PasteLink into those fields. Then when you Refresh your data from the Database query your cells are automatically refreshed as well. Also, if you dump it all on one sheet, on the sheet where you have it formatted just type in that Sheet and Location in the Formula Bar and then Select that and Drag to copy it down as far as you wish.
Like this....:=DUMPSHEET!H21

Dump Sheet is called DUMPSHEET. You Import your Access data on this sheet. Then, on the sheet you have already formatted to have data in certain places Select those cells and type the above in BOLD into the Formula Bar. Aw, what the heck...I just made a sample spreadsheet of what I am talking about. Look at the first sheet as if it were where you really want your data. The other sheet, DumpSheet is where you pull in the raw data from your Access Query. I did it 3 different ways, but with the same results. Hint: on the ActiveSheet mouse over the first cell of data where the lil red corner (Comment) is and see what it says. Hope this helps you out in some way.

have a nice one,
BUD

but are you saying that I will need to create a seperate query for EACH cell I want to move? ......

I have about 30 cells needing to copy...

No, you only import the main data. I would suggest using formulas to show select data on another page or area. Bud attached an excellent example in his reply. Thanks bud.

When you get the import and formulas working perfectly you can hide the Dumpsheet from view (if you want to) so you do not see the raw data. [Fromat->Sheet->Hide] Menu to hide a sheet.


/

Hi savbill and thanks for stepping in for me. Databasemon, as Savbill said, you only pull in that ONE query with all the data you wish. Once you have that done you can pull onto the other sheet whatever you wish from that Query. From my experience it is just easy enough to bring over that Query with EVERYTHING, that way you can pick and choose at a later date. OH, one more thing, SAVE that Query inside of Excel, then you are tied into the Access Query without having to do all that labor anymore. And, you set Excel to Refresh your link for you, and I think you have the option of how often you want it refreshed, in terms of minutes, seconds, when worksheet is opened...etc.
Hope we gave you enough to help you out.
have a nice day everyone....
BUD