Formatting Excel - Getting a Runtime error 5

[BillDickenson]

I am trying for format some excel from Access and i keep having trouble with the formatting commands. So I have two questions...

Periodically, someone suggests inserting the number code instead of the defined constants. Where do I look that up ?

And; does anyone see what I'm going wrong here. It fails in the line below the comment "the next line fails". Thanks

Public Function FormatSRCSummary()
Dim iFilename As String
Dim szQueryName As String
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object


If gszWorksheetPath = "" Then
Call GlobalLoad
End If

iFilename = gszWorksheetPath & "SRC Summary.XLS"
szQueryName = "qTotalBNPbyMonth"

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If TypeName(xlApp) = "Nothing" Then
'Excel was not open
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0

Set xlWB = xlApp.WorkBooks.Open(iFilename)
Set xlWS = xlWB.Worksheets(szQueryName)

With xlWS.Range("E:E,F:F,H:H,I:I,K:K,L:L,N:N,O:O,R:R,Q:Q,T:T,U:U,X:X,Y:Y,AA:AA,AB:AB,AC:AC,AD:AD,AF:AF,AG:AG,AI:AI,AJ:AJ,AL:AL,AM:AM,AO:AO,AP:AP")
.Select
.FormatConditions.Delete
'The next line fails
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"
.FormatConditions(1).Interior.ColorIndex = 6
End With

With xlWS.Range("G:G,J:J,M:M,P,S:S,V:V,Y:Y,AB:AB,AE:AE,AH:AH,AK:AK,AN:AN,AQ:AQ")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"
.FormatConditions(1).Interior.ColorIndex = 3
.Interior.ColorIndex = 15
End With
xlWS.Columns("E:AQ").NUMBERFORMAT = "$#,##0_);($#,##0)"
With xlWS.Rows("1:1").Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
xlWS.Range("A1:AQ16").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, _
9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35 _
, 36, 37, 38, 39, 40, 41, 42, 43), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True
xlWS.Columns("A:AQ").EntireColumn.AutoFit

With xlWB
.SaveAs iFilename
.Close SaveChanges:=True
End With

Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Function

 

[PHV]

Where do I look that up
When in the Excel VBE open the Immediate (debug) window (Ctrl+G) and type the following
? xlCellValue

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

You seem to be using late binding - you are declaring the Excel objects as objects, and not excel.appliaction, excel.workbook...

When using late binding, the automating application is not aware of any of the objects, until they are instantiated through either createobject or getobject - AND - more important, none of the constants of the automated application is known.

So, in Access xlCellValue, xlLess and the other constants, does not have any value, and will cause runtime errors, so you will need to replace them with the value they represent. PHV's method is OK, or you could hit F2 to get the object browser and search for the constants.

Had you had a reference to Excel, and used early binding, you would have been able to use the constants, but then again, you would probably not be able to open this on versions 2000-2003 without the reference hassle...

Roy-Vidar

 

[BillDickenson]

Thank you both for excellent suggestions. It's all working. Thanks again