I've got some code that exports recordsets from Access into Excel spreadsheets.

Every time the user exports a recordset, the program creates a new instance of Excel because this is what I've told it to do; I don't know how to ask it to check for existing instances of Excel and to use an existing instance if it's present or to create a new one if there's none present.

Nobody around me knows how to do this and I haven't found anything on the net that works; can anyone help?

Thanks!

I've still been searching on the net, haven't found anything yet...any takers?

Can you post your current code?

This is a complete and utter hack; it seems to work but it's very unstable. I was hoping there was a considerably more elegant way to do this. The sub assumes an instance of Excel exists and if it doesn't it errors and creates one but the error is not seen by the user.

Public Sub testexcel()

On Error GoTo TestExcel_Err

Dim db As Database
Dim rst As ADODB.Recordset

Dim strSource As String
Dim i As Integer

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set db = CurrentDb()

strSource = DLookup("AccessObject", "tblReports", "ReportName='" & _
gVarReportSelected & "'")

Set rst = New ADODB.Recordset
rst.Open Source:=strSource, ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4. 0; " & _
"Data Source=" & CurrentDb.Name

Set xlApp = GetObject(, "Excel.Application")

testexcel:
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet

For i = 0 To rst.Fields.Count - 1
With xlSheet
.Cells(1, i + 1).Value = rst.Fields(i).Name
.Range("A2").CopyFromRecordset rst
.Columns(i + 1).AutoFit
End With
Next i

xlApp.Visible = True

TestExcel_Err:
Select Case Err.Number
Case 429
Set xlApp = CreateObject("Excel.Application")
Resume testexcel
Case Else
Exit Sub
End Select


End Sub

Nevermind...2 weeks later, I've found the answer in O'Reilly's Access Cookbook, chapter 11.