Excel Automation: I am confused

[Jean]

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

[RoyVidar]

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

[Jean]

Hey Roy,

Thanks, that was the right solution!

Rgds

J