Output to a custom excel sheet

[Dazza666]

Hi,

I've seen people output data from an access database to an excel spreadsheet. This is not a standard export though this is exported to a custom sheet with totals etc. This would be great for us, I was curious how this is accompolished, it it through XML?

We use varying versions of excel and access (2000 - 2007)

Can someone give me an idea of how to get started with something like this?

As always any help is appreciated
thanks!

 

[pbaldy]

It's generally done with what's called "automation". Search here and on the MS site and you should turn up the code to do it. Post back if you get stuck.

 

[Dazza666]

It's generally done with what's called "automation". Search here and on the MS site and you should turn up the code to do it. Post back if you get stuck.

Thank you very much for that, I found enough information to give me a leg up, i'm happy to work out the details.

Just quickly, is it possible to copy data to a worksheet from serveral access sources? (such as multiple queries and / or tables)

thanks

 

[pbaldy]

Sure; if you're using automation you're probably going to be using a recordset to get the information from Access. You can simply open more than one recordset and pull from each as desired.

 

[Dazza666]

Sure; if you're using automation you're probably going to be using a recordset to get the information from Access. You can simply open more than one recordset and pull from each as desired.

Brilliant! I'll enjoy learning about this now.

Thank you once again for your help

 

[Atomic Shrimp]

I'm in the process of doing my first Excel automation project right now and it's really cool to see the amount of control you can get over the presentation of your data this way.

One tip that has helped quite a lot is: If you want to know how to do something with automation, go into Excel and record it in a macro, then crib the code from the macro for your Access application - it needs a bit of tweaking to get Access to do it, but not much.

 

[pbaldy]

No problem, and like I said, post back if you get stuck.

 

[pbaldy]

That's a good point Mike; I should have mentioned it. That's what I do too.

 

[Dazza666]

I'm in the process of doing my first Excel automation project right now and it's really cool to see the amount of control you can get over the presentation of your data this way.

One tip that has helped quite a lot is: If you want to know how to do something with automation, go into Excel and record it in a macro, then crib the code from the macro for your Access application - it needs a bit of tweaking to get Access to do it, but not much.

cool I'll keep this in mind

cheers,