Export query to Excel and apply conditional formatting

[mcpearce]

Hi All,

Having some trouble figuring out how to automate conditional formatting of an excel spreadsheet after having output it from my database.

I have cobbled together some code that effectively exports the query to Excel and applies some of the formatting (i.e. column width, row height, hides column C) I copied from a recorded macro, but when it comes to highlighting rows where the day name is "Saturday" or "Sunday" I get nothing. When I close the spreadsheet I see an access error message saying "invalid call or argument"... I'm guessing that is a clue to what I am doing wrong.

My code is...

Private Sub Command592_Click()
Dim xl As Object
Set xl = CreateObject("Excel.Application")
xl.Application.ScreenUpdating = False
xl.Visible = True
xl.Application.ScreenUpdating = True
On Error GoTo Command592_Click_Err
DoCmd.OutputTo acOutputQuery, "qryRC2_CheckedOnly_Crosstab", "ExcelWorkbook(*.xlsx)", "C:\temp\Resource_Chart2.xlsx", False, "", , acExportQualityScreen

xl.Workbooks.Open ("C:\temp\Resource_Chart2.xlsx")

xl.Cells.Select
xl.Selection.ColumnWidth = 35
xl.Selection.RowHeight = 15


xl.Range("A:A,B:B").Select
xl.Range("B1").Activate
xl.Range("A:A,B:B").EntireColumn.AutoFit
xl.Columns("C:C").Select
xl.Selection.EntireColumn.Hidden = True
xl.Cells.Select
xl.Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=if($B1=""Saturday"",True,if($B1=""Sunday"",True, False))"
xl.Selection.FormatConditions(Selection.FormatCond itions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
End With
xl.Selection.FormatConditions(1).StopIfTrue = False
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
xl.Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
xl.Range("D2").Select
xl.ActiveWindow.FreezePanes = True

Command592_Click_Exit:
Exit Sub
Command592_Click_Err:
MsgBox Error$
Resume Command592_Click_Exit
End Sub

Any ideas? Suggestions?

Regards,

Mitch

[mcpearce]

I have tidied up the code little below... still no joy.

Private Sub Command592_Click()

Dim xl As Object

Set xl = CreateObject("Excel.Application")
xl.Application.ScreenUpdating = False
xl.Visible = True
xl.Application.ScreenUpdating = True

On Error GoTo Command592_Click_Err

DoCmd.OutputTo acOutputQuery, "qryRC2_CheckedOnly_Crosstab", "ExcelWorkbook(*.xlsx)", "C:\temp\Resource_Chart.xlsx", False, "", , acExportQualityScreen

xl.Workbooks.Open ("C:\temp\Resource_Chart.xlsx")

xl.Cells.select
xl.Selection.ColumnWidth = 35
xl.Selection.RowHeight = 15
xl.Range("A:A,B:B").select
xl.Range("B1").Activate
xl.Range("A:A,B:B").EntireColumn.AutoFit
xl.Columns("C:C").select
xl.Selection.EntireColumn.Hidden = True
xl.Range("D2").select
xl.ActiveWindow.FreezePanes = True

"...... Applies all formatting till this point. Conditional formatting below not applied. Error 'invalid procedure call or argument'?"

xl.Cells.select
xl.Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($B1=""Saturday"",TRUE,IF($B1=""Sund ay"",TRUE,FALSE))"
xl.Selection.FormatConditions(Selection.FormatCond itions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
End With

xl.Selection.FormatConditions(1).StopIfTrue = False

Command592_Click_Exit:
Exit Sub

Command592_Click_Err:
MsgBox Error$
Resume Command592_Click_Exit

End Sub

[June7]

What is the TRUE,FALSE for? What does StopIfTrue accomplish? Stop what?

Formula:="=IF(OR($B1=""Saturday"",$B1=""Sunday""), TRUE,FALSE)"

Why not just:
Formula:="=OR($B1=""Saturday"",$B1=""Sunday"")"

Code works from Excel module. Don't know why Access fails.

[mcpearce]

Hi June7,

The "xl.Selection.FormatConditions(1).StopIfTrue = False" appeared in the macro I recorded in excel, so I just copied it across. Deleted it from my code and it made no difference.

I tried to use "OR" in my conditional formatting directly in excel, but it didn't like it. I know it's a bit messy using two "ifs", but its the only way that seems to work for me.

I have read lots of posts about problems with applying conditional formatting, but no one else seems to be mentioning the "Invalid procedure call or argument" error message.

Stumped!

[June7]

I tested the OR expression in the conditional formatting and it worked. Even after using code to set it.

Since you are opening an existing workbook, why not put the formatting code in the workbook? And maybe even have the workbook pull the data instead of Access pushing?