Access 2007 to format conditionals in Excel

[bposkie]

I have an Access 2007 database where I generate and export a report to Excel 2007. I then have a button on my report to open and format the exported file. The file opens just fine. The initial formating is completed, but then it hacks on defining conditional formats. My conditional formating is pretty simple - comparing all the date fields in column G with 4 other date fields (A1, B1, C1, and D1). The error is one I found to be common when searching the net 'Run-time error '5': Invalid procedure call or argument' I have not been able to find another thread with a solution that fitls my issue. Below is my code - it stops on the first call to Add a Formula for conditional format. I followed other advise of recording a macro in Excel and then copy/paste to Access, but I've made some minor modifications not pertaining to the conditional formulas. Any help is much appriciated.

Private Sub btnExport_SB_Click()
Dim objXLApp As Object
Dim objXLBook As Object
Dim strFormula1 As String
Dim strFormula2 As String
Dim strFormula3 As String
Dim strFormula4 As String

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("D:\data\WorkData\Projects\ProjectPlanning\qryProjectMSTR3_SB.xls")
objXLApp.Application.Visible = True
'strFormula1 = "=IF(G1<$D$1,1,0)"
'strFormula2 = "=IF(G1<$C$1,1,0)"
'strFormula3 = "=IF(G1<$B$1,1,0)"
'strFormula4 = "=IF(G1<$A$1,1,0)"
With objXLApp
'Code from Excel Macro
.Rows.AutoFit
.Rows("2:2").Font.Bold = True
.Range("A11").NumberFormat = "m/d/yyyy"
.COLUMNS("G:G").NumberFormat = "m/d/yyyy"
.Range("A2").FormulaR1C1 = "Project Manager"
.Range("B2").FormulaR1C1 = "Oracle Number"
.Range("C2").FormulaR1C1 = "Customer"
.Range("D2").FormulaR1C1 = "Project Number"
.Range("E2").FormulaR1C1 = "Project"
.Range("K2").FormulaR1C1 = "Cost Center"

.COLUMNS("G:G").Select

With .COLUMNS("G:G")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(G1<$D$1)=1"
.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
End With
With .COLUMNS("G:G").FormatConditions(1).Font
.Color = -1003520
.TintAndShade = 0
End With
.COLUMNS("G:G").FormatConditions(1).StopIfTrue = True
.COLUMNS("G:G").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(G1<$C$1)=1"
.COLUMNS("G:G").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With .COLUMNS("G:G").FormatConditions(1).Font
.ThemeColor = -11489280
.TintAndShade = 0
End With
.COLUMNS("G:G").FormatConditions(1).StopIfTrue = True
.COLUMNS("G:G").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(G1<$B$1)=1"
.COLUMNS("G:G").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With .COLUMNS("G:G").FormatConditions(1).Font
.Color = -15773952
.TintAndShade = 0
End With
.COLUMNS("G:G").FormatConditions(1).StopIfTrue = True
.COLUMNS("G:G").FormatConditions.Add Type:=xlExpression, Formula1:="=IF(G1<$A$1)=1"
.COLUMNS("G:G").FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With .COLUMNS("G:G").FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
.COLUMNS("G:G").FormatConditions(1).StopIfTrue = True
ActiveSheet.Outline.ShowLevels RowLevels:=1
End With
'Done Excel Formating
'wbk.Close SaveChanges:=True
'xlObj.Quit

End Sub

 

[bposkie]

BTW...I have tried altering the formual to "=IF(G1<$D$1,1,0" as it usually is defined within Excel, but it has not made a difference. I also tried defining a string to hold the formula, but that didn't work either.

 

[bposkie]

Figured it out on my own.

Solution - identify xlExpression (Const xlExpression as Long = 2)

I had a few other quirks in my code, but they were easy to fix once I defined xlExpression. Below is my final code with the fixes and a little cleanup.

Private Sub btnExport_SB_Click()
Dim objXLApp As Object
Dim objXLBook As Object
Dim strFormula1 As String
Dim strFormula2 As String
Dim strFormula3 As String
Dim strFormula4 As String
Const xlExpression As Long = 2
Dim clrRed As Long
Dim clrOrange As Long
Dim clrGreen As Long
Dim clrCyan As Long
Dim clrBlack As Long

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("D:\data\WorkData\Projects\ProjectPlanning\qryProjectMSTR3_SB.xls")
objXLApp.Application.Visible = False
strFormula1 = "=IF($G1<=$D$1,1,0)"
strFormula2 = "=IF($G1<=$C$1,1,0)"
strFormula3 = "=IF($G1<=$B$1,1,0)"
strFormula4 = "=IF($G1<=$A$1,1,0)"
clrRed = RGB(255, 0, 0)
clrOrange = RGB(255, 165, 0)
clrGreen = RGB(0, 238, 0)
clrCyan = RGB(0, 238, 238)
clrBlack = RGB(0, 0, 0)

With objXLApp
'Code from Excel Macro
.Rows.AutoFit
.COLUMNS("G:G").Font.Color = clrBlack
.Rows("2:2").Font.Bold = True
.Range("A11").NumberFormat = "m/d/yyyy"
.COLUMNS("G:G").NumberFormat = "m/d/yyyy"
.Range("A2").FormulaR1C1 = "Project Manager"
.Range("B2").FormulaR1C1 = "Oracle Number"
.Range("C2").FormulaR1C1 = "Customer"
.Range("D2").FormulaR1C1 = "Project Number"
.Range("E2").FormulaR1C1 = "Project"
.Range("K2").FormulaR1C1 = "Cost Center"


With .COLUMNS("G:G").FormatConditions
.Delete
.Add xlExpression, , strFormula4
End With
With .COLUMNS("G:G").FormatConditions(1).Font
.Color = clrRed
.TintAndShade = 0
End With
.COLUMNS("G:G").FormatConditions(1).StopIfTrue = True
With .COLUMNS("G:G").FormatConditions
.Add xlExpression, , strFormula3
End With
With .COLUMNS("G:G").FormatConditions(2).Font
.Color = clrOrange
.TintAndShade = 0
End With
.COLUMNS("G:G").FormatConditions(2).StopIfTrue = True
With .COLUMNS("G:G").FormatConditions
.Add xlExpression, , strFormula2
End With
With .COLUMNS("G:G").FormatConditions(3).Font
.Color = clrGreen
.TintAndShade = 0
End With
.COLUMNS("G:G").FormatConditions(3).StopIfTrue = True
With .COLUMNS("G:G").FormatConditions
.Add xlExpression, , strFormula1
End With
With .COLUMNS("G:G").FormatConditions(4).Font
.Color = clrCyan
.TintAndShade = 0
End With
.COLUMNS("G:G").FormatConditions(4).StopIfTrue = True
.ActiveSheet.Outline.ShowLevels RowLevels:=1

.Rows("2:2").FormatConditions.Delete
End With

'Done Excel Formating
objXLBook.Close SaveChanges:=True
objXLApp.Application.Visible = True
'objXLApp.Quit

End Sub

 

[boblarson]

If you use late binding you can't use Excel constants as Access has no clue as to the constant values; you have to define them. If you use early binding then Access knows what you are talking about.

 

[boblarson]

Oh, and next time you post a big set of code - USE CODE TAGS. It makes it more readable.