Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100

    Unanswered: Find instances of Excel in VBA?

    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!


  2. #2
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    I've still been searching on the net, haven't found anything yet...any takers?

  3. #3
    Join Date
    Mar 2006
    Posts
    163
    Can you post your current code?

  4. #4
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Quote Originally Posted by norie
    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

  5. #5
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    100
    Nevermind...2 weeks later, I've found the answer in O'Reilly's Access Cookbook, chapter 11.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •