Exporting from Access form to Excel worksheet

  • Thread starter Thread starter jdtivoli
  • Start date Start date
J

jdtivoli

I have created a form in Access 97 based upon a query.
I want to export the data on this form to a pre-designed
Excel worksheet. I would prefer to do this via a control
button on the Access form, which when pressed would open
the Excel worksheet and automatically insert the data
from the Access form.

Any ideas would be appreciated,

Jesse
 
1. You export tables or queries, not forms.

2. Use TransferSpreadsheet command.

3. Automate (if you have to.)
Articles on How to Automate Excel:

I would like the data to simply be placed on a worksheet
that I've already spent time formatting (i.e. column widths, bolded column
headings...etc.)
Is there a way to do this?

You can't do this with TransferSpreadsheet.
You'll need to write VBA code that uses Automation to control Excel and
ensure
that the data ends up where you want it.

If you're new to Automation, here's a useful article giving the
basics of controlling Excel from Access:

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/default.aspx?scid=KB;en-us;123859

ACC2000: Using Automation to Create and Manipulate an Excel
Workbook (Q210148)
http://support.microsoft.com/default.aspx?scid=kb;en-us;210148

ACC: Using Automation to Create and Manipulate an Excel
Workbook (Q142476)
http://support.microsoft.com/default.aspx?scid=kb;en-us;142476

Recent versions of Excel have a Range.CopyFromRecordSet method
that can be very handy for importing data from Access.
 
I tried running the first sample you referred me to
(http://www.mvps.org/access/modules/mdl006.htm). No
luck.
I set it up as a control button on a form in a sample
database. When I clicked on the control button, I
received and error message saying "Compile Error - Sub or
Function not defined". Then it highlights the Sub
sTestXL_Click() title.

Next I tried the following sample;
(http://support.microsoft.com/default.aspx?scid=kb;en-
us;14276 Entitled Using Automation to Create and
Manipulate and Excel Workbook) No luck here either.
I set it up exactly as the sample showed. But I received
an error saying "Compile Error - Variable not defined".
Then it highlights the next to the last line of code
which says Text1.SetFocus

Help please,

Jesse
 
Back
Top