Excel Link

[carolinera]

I understand that Access is the much better program but my client is insisting on a link to an excel spreadsheet. Never having done one of these from Access I'm not sure how to go about it.

I have put together a query that has member id, meeting id, reg type, reg amt, sumofreg amt, refund, sumofrefund amt, net amt, sumofnet amt and refund reason.

My question? How do I put these into an excel spreadsheet. My client has very specific idea of how the spreadsheet should look and what information he wants. When I use the office options and analyze by excel I only get the first line. I know I'm doing something really wrong but for the life of me I'm at a loss.

Thx for the help

- Caroline

 

[Dugantrain]

Try starting from Excel and importing from Access:
In your Excel Worksheet, select Data ->Get External Data->New Database Query->MS Access Query->(Connect to your database)
->(Select the query that you've written). If this works the way you want it to, you can have it automatically refresh every time you open the worksheet.

 

[gblack]

OK here’s a way:

It involves using two separate spreadsheets (one you export your query to, and the other one is formatted the way you want and links to the prior).

Access doesn’t allow for too much pretty excel formatting.

Use the transferSpreadsheet macro:
Goto the Macro section >> open a new macro >> Click the dropdown box >> Pick: TransferSpreadsheet >> Look down at the bottom and fill out the necessary information:

Transfer type: Export
Spreadsheet Type: Microsoft Excel (whatever version you have…or as High as it allows)
Table Name: Name of your query
File Name: “Full\Path\PlusWhateverYouWantToCallYourFile.xls”
Has Field Name: Yes if you want field names…no if you don’t
Range: Leave Blank

Once you successfully transfer the Spreadsheet, You may then link the pertinent data to the Spreadsheet that is formatted like your client wants.

HTH
Gary

 

[carolinera]

Thank you both for your suggestions. I tried both but of course I forgot to let you know that my query is a "dynamic query" that prompts the use for a meeting id number. That means that the only information that was imported was the headings. Any other suggestions....I'm starting to pull my hair out. Thank you!!!!!!!

 

[spindar]

I've just managed to do something similar in my db.

This is how I did it

(1) use the macro command called TRANSFRSPREADSHEET
(2) In Transfer Type enter Export
(3) In Spreadsheet type put Excel
(4) In Table Name put the name of the query you have used
(5) In file name put whatever you wish to call it
(6) In Has field names put Yes

I then made a command button that runs this macro, and I also put in a hyperlink to the file. So by clicking the button I exported the data and opened the file.

 

[gblack]

Simply make a Make table query to run based off your dynamic query. Then export the table you make...