Manipulation Excel/Outlook Objects in Access

[drkestrel]

I am trying to manipulate some Excel worksheets and outlook objects for sending mail from the VBA in Access 97.

Is this impossible without using VB directly or something?? Cos I can't even do something like the following results in complilation errors complaining that Worksheet is unknown-
Dim myWorksheet as Worksheet. I basically want to do things like select different cells, and process them, and with Outlook Objects, I want to use them to send mail

If these really are impossible tasks without using VB, then are there any utilities for porting the code from Access VBA's Form to VB?? or would Copy & Paste simply do?? (Note that there are differences like DoCmd OpenForm "myForm" in VBA and myForm.Open() in VB)!

MAny Thanks! [sig][/sig]

 

[FatherJack]

I think that you may have ommitted to reference the excel application. When in the module view Go to Tools / References and check the Microsoft Excel row. When you go back to the module the xl...... code should allow excel functions to work effectively

Father Jack [sig][/sig]

 

[Quehay]

You've got to get reference to the Excel app and its objects to be able to do things in code. Look up "automation" in the help--there are good examples there. You can also use sendkeys and macros but I haven't done this. Using the macro recorder in Excel is a great way to get working code examples, although there will be extra lines that aren't needed sometimes.

Here's some code I use for export--it shows how to get reference to an existing XL sheet/template. There's a reference to a constant XLpath that doesn't show here. The GetObject method will either use the open instance of Excel or create a new instance as long as you pass reference to an actual workbook.

Hope this is useful:
[tt]
Public Sub NewExportToExcel(QryName As String, TmpltName As String, SaveName As String)

'Exports the qry_PatientVisits to an Excel template
'and prints it

On Error GoTo ExportToExcel_Err
'DAO object variables
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
'Utility variables
Dim RowCount%, RowNum%, ColCount%, ColNum%, i%
Dim PrintNow%
'Excel object variables
Dim objXLWkb As excel.Workbook, objXLWksht As excel.Worksheet
Dim strSaveAs As String
Dim rngXLCurrent As excel.Range


DoCmd.Hourglass True

Set db = CurrentDb()
Set qdf = db.QueryDefs(QryName)

' Open, PatientVisits.xls (serves as a template) and make it visible
Set objXLWkb = GetObject(XLPath & TmpltName)

With objXLWkb.Application
.Visible = True
.ScreenUpdating = False
End With

objXLWkb.Parent.Windows(1).Visible = True

' Open the qry_PatientVisits recordset
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
ColCount% = rs.Fields.Count

Set objXLWksht = objXLWkb.Worksheets("PatientVisits&quot

objXLWksht.Activate
rs.MoveLast
RowCount% = rs.RecordCount [/tt]
[sig][/sig]