Excel 2003 VBA - Method to duplicate this code that select and colors rows
- by Justin
so this is a fragment of a procedure that exports a dataset from access to excel
Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objxls As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Set rs = CurrentDb.OpenRecordset("qryOutput", dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objxls = New Excel.Application
objxls.Visible = True
With objxls
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
On Error Resume Next
.Range(.Cells(1, 1), .Cells(intMaxRow, intMaxCol)).CopyFromRecordset rs
.Name = conSHT_NAME
.Cells.WrapText = False
.Cells.EntireColumn.AutoFit
.Cells.RowHeight = 17
.Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 10
End With
.Rows("1:1").Select
With Selection
.Insert Shift:=xlDown
End With
.Rows("1:1").Interior.ColorIndex = 15
.Rows("1:1").RowHeight = 30
.Rows("2:2").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
.Rows("4:4").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
.Rows("6:6").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
.Rows("1:1").Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With
End With
End If
Set objSht = Nothing
Set objWkb = Nothing
Set objxls = Nothing
Set rs = Nothing
Set DB = Nothing
End Sub
see where I am looking at coloring the rows. I wanted to select and fill (with any color) every other row, kinda like some of those access reports. I can do it manually coding each and every row, but two problems: 1) its a pain 2) i don't know what the record count is before hand.
How can I make the code more efficient in this respect while incorporating the recordcount to know how many rows to "loop through"
EDIT: Another question I have is with the selection methods I am using in the module, is there a better excel syntax instead of these with selections....
.Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 10
End With
is the only way i figure out how to accomplish this piece, but literally every other time I run this code, it fails. It says there is no object and points to the .font ....every other time? is this because the code is poor, or that I am not closing the xls app in the code? if so how do i do that?
Thanks as always!
