Jean wrote in message
<11*********************@g44g2000cwa.googlegroups. com> :
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
If this is still a problem, ...
In this line
Set xlSourceRange2 = .Range(Range("g2"),
Range("g2").End(xlDown))
you have two unqualified references to the Excel Range object,
which I think might be the probable culprit - two small "dot's" (.)
might make the difference:
Set xlSourceRange2 = .Range(.Range("g2"),
..Range("g2").End(xlDown))
Or if the With block object isn't the correct one, insert the correct
reference.
--
Roy-Vidar
