Move data from Access query to Excel sheet 3

[nja5150]

Hello-

This is a very basic question. I'm looking for any ideas.

I have an access query that I need to use to fill data in on an excel worksheet.

This work sheet already has some headers and other stuff already created at the top of the sheet (like title and company info).

I need to start filling data about 10-15 spaces down, so I'll need some fine control of what cells get filled.

The other problem is access can't mess with these cells formatting and parameters I just need it to fill data only.

The sheet needs to be reused week to week.

My thoughts are to use a blank template sheet in excel with my parameter query. Fill the data where I need it, and then generate a new file with the appropriate name (the date entered in my parameter query).

I'd prefer to generate a new excel sheet, or work from a template each time within access. That seems more elegant than starting in access, coding to start excel, updating linked data from access on an excel sheet, and running macros to generate the new file.

Maybe the less elegant solution is the better one?

Is this too crazy to take on? Any other alternatives I haven't discussed here that will work?

Thanks for any help you guys can provide!

 

[alvechurchdata]

Any other alternatives I haven't discussed here that will work?

Neither elegant nor automatic but if the user's got any sort of ability then telling them to cut-and-paste the cells is quick and cheap.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[pbrodsky]

Other alternatives:
1. import the data to a separate worksheet (Data ! get external data) and then link from your sheet to the appropriate data. The query results could be updated just by doing a Refresh Data in Excel.
2. Write VBA code in Excel (ADO or DAO) to retrieve specific data from the query and stick in the desired cells.

Either of these is fairly easy.

 

[nja5150]

Alvechurchdata, yes I could handle the cut/copy and paste method. Unfortunately I need to make this, "push button" simple for the masses. Thanks for your input though!

Pbrodsky, thanks! I didn't even think about using VBA code in Excel to retrieve the specific data I needed. I forget that you can use it in either app.

Thanks guys!

 

[willir]

Approaching it from the other end.

Create an ODBC entry for your Access database. Then in Excel, "Data" -> "Get External Data" -> "New Database Query". Use the wizard to select the table or tables.

Spend time "sprucing-up" the query to ensure only the proper fields are brought over. Ensure that the date field is included, and add a criteria for it such as Month(YourDateField) and enter 2 for February. Save the DQY query with the appropriate name.

NOW, your user has to...
"Data" -> "Get External Data" -> "New Database Query"

At the prompt, "Choose Data Source", they select the "Queries" tab and navigate to the DQY query that was saved. When the query pops up, they can then change the Month from 2 -> 3.

The data is then pasted into the spreadsheet without affecting the formatting.

Richard

Since this is within Excel, you can create a macro or two to automate part of the process.