fill a few cells in an excel spreadsheet from a form

[Guest]

What is the easiest way to populate a few cells in an excel
spread sheet from an Access form via Vbasic?

 

[John Nurick]

Automation. See http://www.mvps.org/access/modules/mdl0006.htm for
basics.

This article discusses various methods of using VB(A) to get data into
Excel. http://support.microsoft.com/?id=247412 One method, "Transfer
data to a worksheet by using ADO" no longer works quite as described;
the article should be updated soon, so check the "Last Review" date on
it.

 

[Guest]

Since I'm real new to this, how would I open an existing XL spread sheet.
I read the article "Methods for Transfering Data to Excel from Visual Basic"
you
suggested. I am lost since I don't know any of the 'oExcel' commands, or even
where to find info on them. Access help is of no use in this area.

Any help you give is appreciated.

 

[John Nurick]

Here's the "Use automation to transfer data cell by cell" approach from
http://support.microsoft.com/?id=247412 modified in two ways.

First, it uses what's called "early binding". Don't worry about the
technicalities of this just yet; the important thing at this stage is
that it makes the Intellisense dropdowns in Access's VBA editor work
with the Excel objects too. To make it work, you'll need to go to
Tools|References and set a reference to the Microsoft Excel Object
Library.

Second, it uses an existing workbook.

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

'Open existing workbook
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("D:\Folder\File.xls")

'Add data to cells of a worksheet
Set oSheet = oBook.Worksheets("SheetName")
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"

'Save the Workbook and Quit Excel
oBook.Save
oExcel.Quit

All the 'oExcel' stuff is standard Excel VBA code. Setting the reference
to the Excel library will (with luck, in some versions of Office) make
help available if you select a term and press F1, but you'll probably
also need to use Excel VBA help.