My code is in Access 2003 VBA.
It creates an Excel application - the report data (formula is perfect)
Wondering if passing the excel objXL (created in click event) is the problem?
At the end of the Access form, all objects (included objXL) are set to Nothing
Funny, it accuratelly puts values and formula's in the cells.
- The first time through, it also puts a border around each cell with a value.
- The second and more times, it just ignores only the borders.
- every thing is fine, just the borders the second, third and beyond times.
Rebooting the PC; the borders work fine the the first run.
#Private Sub AddFormulaToDeals(objXL As Object)
' ......... declare variables
objXL.Worksheets("Deals").Range("R3").Select
objXL.Worksheets("Deals").Range("R3").Activate
'objXL.ActiveCell.FormulaR1C1 = "=SubTotal(R[5]C[18]:R[intFindLastRow]C[18)"
objXL.ActiveCell.Formula = "=SUBTOTAL(9,R5:R" & intFindLastRow & ")"
objXL.ActiveCell.Style = "Currency"
objXL.ActiveCell.Interior.ColorIndex = 15
objXL.ActiveCell.ColumnWidth = 15
objXL.Worksheets("Deals").Range("R2").Select
objXL.Worksheets("Deals").Range("R2").Value = "Grand Cost"
With objXL ' only works first time With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With
objXL.Worksheets("Deals").Range("S3").Select
objXL.Worksheets("Deals").Range("S3").Activate
objXL.ActiveCell.Formula = "=SUBTOTAL(9,S5:S" & intFindLastRow & ")"
objXL.ActiveCell.Style = "Currency"
objXL.ActiveCell.ColumnWidth = 15
objXL.ActiveCell.Interior.ColorIndex = 15
#
Bookmarks