Access to Excel

[Amileaux]

I accidentally posted this under the excel questions - Sorry about that! I currently email access data in "excel" format from Access. Is there a way (while in Access) to send data to excel, make a pivot table, set up page formatting etc and email it then? I have some VBA (Access mostly) knowledge - Thank you. Marie[/b]

 

[garyd1234]

Try Docmd.TransferDatabase

 

[Amileaux]

After I transfer the data to excel - is there a way, with code in Access, to create the pivot table, do other "formatting" (page setup) and then email the "finished product" . OR, do I transfer the data to excel. Then get out of Access, go into Excel and run the code from there. Thank you. Marie

 

[bhoffste]

There's a way to do it from Access, but it's somewhat tedious. You'd have to create a reference to the Excel worksheet and write the PivotTable code from there.

Have you considered creating the PivotTable in Excel beforehand and then simply exporting the data from Access into the source range and refreshing the table? Or better yet, linking the PivotTable to the Access data directly (which has the added benefit of removing Excel's 65,536 record limit). Let me know if any of those approaches appeal to you and I can give you more details....

 

[Amileaux]

Okay - I needed to know how much "effort" it would take.

I currently have worked pivot tables many ways - sometimes I've built them in access (a form) and then just refresh it in excel - but I've never "emailed" these (and I'd have to refresh it before emailing it). I have also been in excel and used msquery to "attach" my pivot tables to data in access, but then I need to open up excel and refresh the data before I email it. If you can suggest the most efficient way to do this with code from access that would be very nice.
Thank you. Marie

 

[bhoffste]

Marie -

I think the easiest thing would be for me to mock something up for you. A very simple database exporting it's data to Excel and creating a PivotTable. You could then alter it to meet your needs. Send me an email - ben at mailblocks dot com (altered to prevent spam spiders from reading the address) and I'll send you a file.

- Ben

 

[Amileaux]

I've tried to send you an email twice and both have come back undeliverable. Soooo - please email the mocked up database to my work address at mamicuc at qwest dot com. However, this should not be alot of work for you - if so, then just let me know - I'm sure I can muddle through this - it will just take me alot longer! Either way - Thank you. Marie