Export Results for Access Query to Excel

[nwinchel]

I am working for an organization that wants me to enable an unsophisticated user to export membership and dollar amount information from a query (payment, totals, etc.) to a monthly report in Excel. I can't figure out (or remember) how to export the data yet preserve the formatting in the Excel report. Every time I export the data it replaces the entire spreadsheet and all formatting so it has to be redone each time. No good. Even when choosing "Save Formatted" in the Export dialog box it sends the data and formats it by itself which is not what I want, either.

(There's probably a simple way to do this but I haven't worked down and dirty with Access 2000 in over a year.)

Thanks in advance.

 

[Anne Troy]

Nancy, you might be better off just exporting the query instead of the report!

Then, maybe you could record her a simple recorded macro that will create subtotals for her. If you need help with that macro, you can email a sample file to dreamboat@nni.com.

Then, her instructions can be:

Run query.
Open MyMacro.xls
Open MyAccessQuery.xls
Hit button (or keyboard shortcut) to run macro.

 

[nwinchel]

Actually, that's what I'm trying to do - just export the data.However, when I do so I lose all the formatting in the Excel s[readsheet originally formatted . There is considerable data - several pages worth- and I don't want to have to adjust columns, change the font, etc., every time someone wants to look at this! (The information is from a parameter query - people enter in date ranges and then want to print off a nicely, formatted report. Is it possible or do I need to take the time and create the report in Access instead of Excel? Might as well if I have to reformat in Excel every time...just looking to do this quickly and ONCE.

Thanks for your quick response.

 

[Anne Troy]

If you're only ever going to do it once, why not try saving it as RTF? Just File-Save as after opening the report. You can create a built-in Access macro that'll do it too.

 

[downwitchyobadself]

Hate to step on Dreamy's toes, she being the Real Reason that The Down hangs out around here, but hey. She's not wrong; but I see what you wanna do. And I don't think it's that hard. (These are famous last words.)

I'm assuming that your "unsophisticated user" and your "organization" wishing to read the data are sharing the same network. If not, then you can pretty much stop reading here and just explain the situation to me.

Excel provides a tool to link to an Access query. Go to Data > Get External Data, and you can link to a query in an Access db, then do whatever you want to do to format the spreadsheet. If you build the query correctly--i.e. with the right parameters for returning this or last month's data, etc. (but I assume that building the query in Access isn't the problem?), the user just opens it up, and the data is there. You can read more about it in Excel help. Try typing link to Access query into Excel Help's Answer Wizard to get some more deets.

Let us know if this won't do it to ya.

 

[nwinchel]

I see what you're sayin'. Right now I have a copy of the database on my local drive to "play" with but yes, we are on the same network and are all using MS Office 2000. I get along real well creating the data source and working through the MS Query Wizard until I click on "finish" when I get an error that says "too few parameters, 2 expected". Excel HELP tells me this is an "error from the driver" but that's about it. What's up with this? I'm using MS Office 2000 on my local system running on W2K Professional.

I think you have a really good idea. I'd just like to see it work! In the meantime, I've been trying to create a report in Access but it's tedious as hell.

Thanks.

 

[downwitchyobadself]

Yes, well, there may be a problem with Excel reading your query. There may also be a problem with your query? I'm assuming it runs fine in Access. Could you describe it a little? How it works?

You might also try going the other way? I'm not sure if the "link" parameter of the TransferSpreadsheet macro action will go the right direction, but if so it's worth a shot. These Excel-Access connections often work better in one direction than the In other, but it's hard to predict which direction, and it's rarely consistent.

You can also export to an Excel file set up to link to a named range on the query page. In other words, if you export the query MyQuery into an existing Excel file, and tell the export wizard not to overwrite the file, it will insert a page called MyQuery into the spreadsheet. If you format a separate sheet of the workbook and link up to the page MyQuery, your users can then delete the exported sheet MyQuery and you can export it again, should preserve the links if you use the sheet name and if you don't change the query name.

Barring all that, the only way to do it will be automation, which involves some VBA, not too complex, but still. I may have something in my Access Developer's Handbook that will do it, let me know.

 

[nwinchel]

Originally posted by downwitchyobadself
Yes, well, there may be a problem with Excel reading your query. There may also be a problem with your query? I'm assuming it runs fine in Access. Could you describe it a little? How it works?

We have a database that tracks the members of our non-profit organization and contains not only their personal contact information but donations, as well. (People don't necessarily donate simply by writing a check for a certain amount. That would be too easy.) Many donate through, say, the United Way or the local Community Foundation. Our accountant wants to be able to plug in any range of dates, say 11/1/01 through 11/30/01 and see who donated how much and through what source during that time. Currently I have set up a report based on a parameter query so the accountant can plug in the required date range. Fields or column headings describe the different ways members donated (United Way, etc.) I also am able to total each row so we can see the total amount that person donated but I also want to see totals for each column. (For instance, how much went through United Way, Community Foundaton, etc. during the time range.) I thought it would be a no-brainer to set this up in Excel. I'm having trouble getting the column totals in my Access report - can't get the correct syntax to do it and I have, like, zero resources (manuals, etc.) here to look it up. This query/report will be run on an ongoing basis and the date range and number of donors will always be different. The accountant sometimes needs this information on the fly and we don't have the time to reformat a report or fool around with something each time we need it. Is this possible without my becoming a programmer?

Thanks for your interest and help.

 

[downwitchyobadself]

Yes, but you will definitely have to spend a little time learning about Access, and maybe Excel isn't the way to go if a sophisticated user always needs the figures on the fly. (Though I understand you want to manipulate?)

Excel ain't lyin to you--it can't process a parameter query where the parameters are built in Access. Don't ask me why, but it can't. So, parameter query is out as a solution.

As for the column totals, you just need to set up a grouping level on your Access report. Grouping by your donation type field, for example, will create a GroupHeader and GroupFooter section on the report; you put a text box in there. If the cashmunny field is called DonationAmt, you set the source of the text box to

=Sum([DonationAmt])

and that's it. Do the same thing in the report footer section (may have to turn it on through the View menu in report design view) to get an overall total. If this keeps you out of Excel, all the better, because then you can make the Access report on a parameter query and run it on the fly with no problems.

But if not, you'll need a little more oompah. I'm a programmer, and I could do this pretty easily, so that means it's somewhere around intermediate. What needs to happen is the following:

1. You pop up a little form in Access where you can select whatever parameters you need: dates or I don't know what.
2. When the user types in those parameters and hits a go button, Access goes out and
3. finds the query to which the Excel file is linked
4. Rewrites its where statement for the parameters on the form
5. Saves and closes the query, and, in an ideal world
6. Opens the Excel file which is linked to it.

Now, if that sounds like Greek to you, we'll see what we can do. But I'd try to fix that report first, and see if your accountant can't do a little less manipulating (sounds fishy anyhoo....)

Keep us informed.

 

[nwinchel]

Originally posted by downwitchyobadself
...Now, if that sounds like Greek to you, we'll see what we can do. But I'd try to fix that report first, and see if your accountant can't do a little less manipulating (sounds fishy anyhoo....)

Keep us informed.

Well, it doesn't sound like Greek but I was able to get the totals I needed on my Access report. I couldn't remember the syntax I needed so thank you for that. (I used to do quite a bit with Access, like, 2-3 years ago but that was with '97.) It's just been a while so I am getting up to speed again...slowly. As little as I know I'm leaps and bounds over anyone else here (so I have no one to ask except you fine people!)

Thanks, again, and you'll probably be seeing my smiling face around these parts often.