Access to Excel - simple copy/paste

W

Wendy Parry

Hi all,
this should be very simple, but it's not always obvious.

I have a form, with a subform. I want to select all the records in the
subform and paste into an Excel worksheet.

I've got the Excel bit okay - but it's the Access part of copying the
records...I guess it should be something to do with the current
recordset....but am a bit lost at that point.

Thanks

Wendy
 
S

Sprinks

Wendy,

If the subform has record selector buttons (RecordSelectors property set to
Yes), then you can select all the records to copy, press Ctrl-C to copy to
the clipboard, and Ctrl-V in Excel to paste them.

Sprinks
 
R

Ron2006

If you are trying to do this via code executed via a button,
One method is docmd.transferspreadsheet acexport
and supply the spreadsheet name and the query name that is the basis
for the subform.

If you are trying to do this manually:
If the subform is in datasheet view, mouse the little box in the top
left hand corner of the subform, (or select a record and do Contorl A
to select all the records) then Control-C and paste into your
spreadsheet.
 
J

John Vinson

I've got the Excel bit okay - but it's the Access part of copying the
records...I guess it should be something to do with the current
recordset....but am a bit lost at that point.
Click to expand...

Take a look at the help topic for "TransferSpreadsheet" in the VBA
editor. You won't copy and paste - you'll be exporting the data to
Excel.

John W. Vinson[MVP]
 
W

Wendy Parry

John said:
Take a look at the help topic for "TransferSpreadsheet" in the VBA
editor. You won't copy and paste - you'll be exporting the data to
Excel.

John W. Vinson[MVP]
Click to expand...
I've had a look at this, it seems to hang Excel at the moment.

What's the difference between TransferSpreadsheet and OutputTo?

How would I transfer all the current records in a subform? My form
switches recordsource in the subform according to a radio button, it's
when the current recordset in the subform is loaded I want to transfer
all the data to Excel - Excel should remain open with the spreadsheet.

Hope this is clear?

Wendy
 
J

John Vinson

I've had a look at this, it seems to hang Excel at the moment.
Click to expand...

Well, then you need to investigate how you're using it, because it
does work... said:
What's the difference between TransferSpreadsheet and OutputTo?
Click to expand...

OutputTo outputs a formatted report. TransferSpreadsheet transfers
data.
How would I transfer all the current records in a subform? My form
switches recordsource in the subform according to a radio button, it's
when the current recordset in the subform is loaded I want to transfer
all the data to Excel - Excel should remain open with the spreadsheet.
Click to expand...

I'd suggest transferring the data from the Recordsource query
directly, rather than from the form - but see the other responses in
the thread, this may be one of the cases where copy&paste is in fact
better.

John W. Vinson[MVP]
 
Top