need to open excel in a window on a form

[JSDART]

Looking for some direction.
I have a form with a command button that opens a particular excel spreadsheet.

the form runs a count of records between a start and stop date.
this number then needs to be entered into this excel spreadsheet.

the way it operated now it opens excel and takes the user completely out of access. I would like to open the excel within the form. I tried an unbound object frame but that will only let you view not update the spreadsheet.
any suggestions would be welcome. Thanks

 

[Fuga]

I don´t know how to have excel in a form, but do you really need to have excel opened at all? Visible I mean? If you´re just going to put a number in a cell then you can do that with vba.

If, however, you need to view it and maybe alter some other cells in the spreadsheet etc then that´s different.

Fuga.

 

[JSDART]

I guess I do not need excel open.
I am trying to run a qry of the numbers of assessments in the month. that number gets pluged into a cell in the excel spreadsheet for that month and the spread sheet calculates the YTD totals.
What VBA would I use to complete this task?

 

[Fuga]

I´ll have a go. Maybe you will have to play around with it a little bit. I usually just create a macro in excel an then run it from access.

as an on_click event of a button (or whatever) on your form:

Dim excelapp As excel.Application
Dim dtperiod as date
Dim strperiod as string
Dim intnumofassessments As Integer
Dim dbltotal As Double

dtperiod = month(date())
strperiod = cstr(dtperiod)
intnumofassessments = DCount("assessmentfield", "assessmentquery", "month([datefield]) =" & strperiod)

Set excelapp = CreateObject("excel.application")
    excelapp.Workbooks.Open ("path to workbook")
    excelapp.ActiveWorkbook.ActiveSheet.Range("your cell").Select
    excelapp.ActiveCell = intnumofassessments

dbltotal = excelapp.ActiveWorkbook.ActiveSheet.Range("e800")

Me.yourtotaldisplayfield = dbltotal
Me.Refresh
excelapp.Quit

If you already have a where clause in your query then the dcount should be dcount ("assessmentfield", "assessmentquery")

Hope it works. Let me know if it doesn´t.

Fuga.