Programming interaction with Excel from Access

[Guest]

The following sub works fine--fromAccess it opens Excel and plops a formula
into a given cell and does the calculating. My question is: Can I make a
form in Access where the user can enter a formula and associate that input
with a variable in the program below so that we can change the formulas in
Excel without changing the sub everytime we want to make that change? I hope
my question is not too confusing? Thanks much.

-Beverly



Sub GoToExcel3()


Dim xlApp As Object
Dim xlbook As Object
Dim xlsheet As Object

On Error Resume Next
Set xlApp = GetObject(, "Excel Application")
If Err Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
xlApp.Application.Visible = True
Set xlbook = xlApp.Workbooks.Open("C:\Bev\Andy\JAM\qryaud-jpytest.xls", 3)

Set xlactivewkb = xlbook.Application.ActiveWorkbook

With xlactivewkb
..Application.ActiveWindow.Zoom = 100
..Application.Sheets("aud-jpy").Select
..Application.Range("b125").Select
..Application.activecell = "=STDEV(T3:T76)*100"
End With


xlbook.Save
'xlbook.Close
Set xlsheet = Nothing
'xlApp.Quit
Set xlApp = Nothing
Set xlbook = Nothing
End Sub

 

[Guest]

Yes, you can put a text box on your form, enter the formula there, and
reference it in your code. One other thing, I would recommend using the
..Formula property when putting a formula in an Excel cell.

.Application.activecell.Formula = Me.txtFormula