Export from Access to Excel

[dodgyone]

I have a form which has a crosstab query result displayed. What I would like is to create a button which.....

Select everything from the datasheet displayed on the form and copy it.

Automatically start up Excel and open an *.xls file which is already created (this has a chart on it which will use the data from Access)

Automatically start the Excel macro (which I have set up in Excel to enter and format the data correctly in the worksheet).

.....Is it possible to do all of this in Access/Excel etc?

Any ideas/code examples/suggestions?

Thanks in advance,

Marcus

 

[kathryn]

Yes, it is possible. What do you want to do with the crosstab data after you have selected it and copied it? Do you want to paste it to a specific range in the .xls?

Basically you need to set a reference to Excel while you have a module page open. Go to Tools->References and select Microsoft Excel 8.0 (I think)

This code will open Excel to your workbook:

dim xlApp as object

Set xlApp = CreateObject("excel.application&quot
xlApp.Visible = True
xlApp.Workbooks.Open "Complete file path here"

However, if you want to manipulate Excel, so you don't necessarily want to make Excel visible. You probably want to set a reference to the workbook.

dim xlApp as object
dim xlWkbk as object

Set xlApp = CreateObject("excel.application&quot
set xlWkbk = xlapp.workbooks.open "Complete file path here"

Then you can reference the sheets and the cells in the workbook. This is all off the top of my head, so it will need tweaking.

Hope this helps.
Kathryn