xls- transfer access 2000 (sorting problem)

[GaryPanic]

Guys
I will have to do a xls workbook every month
what it is , is I have transactions that are in different currencies
Sterling , Dollar, Euro , South african Rands , etc
What I want to know is it possible to transfer out all transactions into a xls(yes) but to seperate out sterling into 1 sheet, dollars into another , etc
(still have not finished ) now we get into a tax problem

(I am in insurnace) so not only do I need these into sepearte sheets by currency, but also by tax as well
example

Sterling + insurnace tax 5%
Sterling + irish levy 2%
Sterling + tax n/a

then I might get
Dollar + insurance prem tax

etc

Ideally each should have its own sheet/tab

Now I am not at this stage yet - but is it do-able (abiout a week or so away)

 

[boblarson]

Gary:

You can't really get separate sheets with the simple DoCmd.OutPutTo or the DoCmd.TransferSpreadsheet. Basically what you will need to do is to use the Excel Object Model to open up an instance of Excel and using a workbook object, add the sheets and enter the data. A bit more complex than I have time to do today, but just thought I'd pass that on.

 

[GaryPanic]

ta for letting me know

i suppose i could do a lot of qrys and tie then to a single command

ie a qry that sorts by
Sterling and tax 1
and a seperate one by tax 2 etc

but i would have to remeber to add a new qry for each currecny and tax - well i'll park this idea for the mo- buckets load more before i get to that stage anyway.

but many thanks for your response ( and the others you and the other guys give)
g

 

[Moniker]

The Excel Object Model is definitely the way to do what you need. It's very simple to do, but just remember that you need to explicitly address each Excel command from within Access. Without doing that, you'll get a completely unrelated error message (something about the remote server connection being broken, which makes no sense). The goofy thing is that it will work on the first run, and then give that weird remote server error on the second run. Also, as with using any object, make sure you explicitly instantiate it and then set it to "Nothing" when you're done.