Excel Automation: I am confused

[antifashionpimp]

Hi all,

I have a problem automating an Excel object from Access.

I have the following fucntion that creates a table in Excel from an Access query:

Function CreateTable2(strSourceName As String, _
      strFileName As String) As Variant

   Dim xlApp As Excel.Application
   Dim xlWrkbk As Excel.Workbook
   Dim xlSourceRange1, xlSourceRange2 As Excel.Range
   Dim CurCell As Object

   On Error GoTo Err_CreateTable2

   ' Create an Excel workbook file based on the
   ' object specified in the second argument.
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
         strSourceName, strFileName, False
   ' Create a Microsoft Excel object.
   Set xlApp = CreateObject("Excel.Application")
   ' Open the spreadsheet to which you exported the data.
   Set xlWrkbk = xlApp.Workbooks.Open(strFileName)

   'Apply formatting depending on type of table
   'Detailtabelle
    With xlApp
       .Worksheets("qryAbtProbKleiner90_PB1_3").Activate
       .ActiveSheet.Range("B7:K7").Select
       .Columns("A:K").EntireColumn.AutoFit
       .ActiveSheet.PageSetup.Orientation = xlLandscape
       ' Determine the size of the range and store it.
       Set xlSourceRange1 = .Selection.Range("a1").CurrentRegion
       With xlSourceRange1
         .BorderAround (xlContinuous)
         .AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
          :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
       End With
       .ActiveSheet.Range("A1:G18").Select
       .Selection.HorizontalAlignment = xlCenter
       .Columns("C").EntireColumn.HorizontalAlignment = xlLeft
       Set xlSourceRange1 = Nothing
       
       'conditional formatting
       ‘********************
       ‘details to follow

  
   ' Save and close the workbook
   ' and quit Microsoft Excel.
   With xlWrkbk
      .Save
      .Close
   End With

   xlApp.Quit

Exit_CreateTable2:
   If Not (xlSourceRange1 Is Nothing) Then
     Set xlSourceRange1 = Nothing
   End If
   If Not (xlSourceRange2 Is Nothing) Then
     Set xlSourceRange2 = Nothing
   End If
   Set xlWrkbk = Nothing
   Set xlApp = Nothing
   Exit Function

Err_CreateTable2:

   MsgBox CStr(Err) & " " & Err.Description
   Resume Exit_CreateTable2


End Function

This works fine, but when I add the following to the above function, where it says “conditional formatting”, I get strange results.

       'conditional formatting
       .ActiveSheet.Range("g2").Select
       Set xlSourceRange2 = .Range(Range("g2"), Range("g2").End(xlDown))
       With xlSourceRange2
         .FormatConditions.Add Type:=xlCellValue, _
                                Operator:=xlLess, _
                                  Formula1:="=Now()"
         With .FormatConditions(1).Font
           .Bold = True
           .Italic = False
           .ColorIndex = 3
         End With
         .FormatConditions.Add Type:=xlCellValue, _
                                Operator:=xlGreater, _
                                  Formula1:="=ReturnBerichtstermin()"
         .FormatConditions(2).Interior.ColorIndex = 6
       End With
       Set xlSourceRange2 = Nothing
    End With

The function executes, but the Excel object is still as a Process in Task Manager. I guess the object is not being managed properly.

Can someone please have a look where I am going wrong?

Regards,
J