Excel Automation from Access

[Guest]

I have data from an outside text file that I'm linking to in Access. I'm
then using the Access VBA to create an Excel workbook (I was going to try to
use TransferSpreadsheet, but the format and cell placement was too tricky).
The problem is that when I try to set the Formula property from Access, it
doesn't work. It just copies it as a string. I posted this in the Access
Programming as well, but since I'm doing it from Access, it gets a bit
complicated and I'm not sure exactly where it fits.

 

[theDude]

Hi Sam,

Copy the following VB code into a module in MS Access. It will creat
an instance of Excel, create a new workbook and insert a simple formul
into cell A1 of the new workbook:


Code
-------------------
Sub AccessToExcel()
Dim xlApp As Object

' Start Excel application...
Set xlApp = CreateObject("Excel.Application")
' Make Excel visible to user...
xlApp.Application.Visible = True
' Open new Excel workbook...
xlApp.Workbooks.Add
' Set relevant worksheet in workbook
xlApp.ActiveWorkbook.Worksheets(1).Activate
' Select cell in workbook
xlApp.ActiveSheet.Range("A1").Select
' Insert formula into active cell...
xlApp.ActiveCell.Formula = "=B1+C1"

End Su