Writing From Access To Excel

[Guest]

I am wondering

Is it even POSSIBLE to write code (possibly SQL) that looks at the given
values in fields on a form in Access and writes the current values in
specified cells of an Excel spreadsheet?

If so, is there a good website or book out there that I could look at for
examples on this. Thanks.

 

[Brendan Reynolds]

Yes, it's possible. Here's a quick example - this uses early binding, which
means you have to add a reference to the Excel object library before this
will work ...

Private Sub Command14_Click()

Dim ExcelApp As Excel.Application
Dim WorkBook As Excel.WorkBook
Dim ExcelSheet As Excel.Worksheet

Set ExcelApp = New Excel.Application
ExcelApp.Visible = True
Set WorkBook = ExcelApp.Workbooks.Add()
Set ExcelSheet = WorkBook.Sheets.Add()
ExcelSheet.Cells(3, 3) = Me.LastName
ExcelSheet.SaveAs CurrentProject.path & "\Test.xls"

End Sub

The Excel 2002 VBA Programmers Reference, published by Wrox Press, is very
good. I haven't read the 2003 edition, but I see that all of the 2002
edition authors are still on board, so I expect it's probably good too.

Here's a link to the Excel developers page at MSDN ...
http://msdn.microsoft.com/office/understanding/excel/default.aspx

 

[Larry Daugherty]

The concept is referred to as "Automation" wherein one application is
client (Access) and another is server (Excel). It requires that you
know how to write the VBA code to achieve your purpose in the Server
application. With a few extra code elements, that code is then
executed at the appropriate places in the Client application.

Look up "Automation" in Help and maybe also on the MSDN and microsoft
support sites.

 

[Tony Toews]

screw excel, access reports are MUCH better. Access forms are MUCH
better.

Yes, but...

I have clients who use reports, PDFs and Excel spreadsheets.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[aaron.kempf]

Tony

I'm so sorry that your idiot clients use Excel. it is a disease.