I'm trying to get the first row in each worksheet to be shaded and have the font bold. Right now, I am able to get the first worksheet changes made, but none of the other worksheets within the same workbook are being changed. Here is what I have written so far for creating the spreadsheet and applying a format.
Public Function WRITE_TO_EXCEL(ByVal dt As System.Data.DataTable, ByVal includeheader As Boolean, ByVal worksheet_index As Integer) As Integer
Dim mrow As DataRow
Dim colindex As Integer
Dim rowindex As Integer
Dim col As DataColumn
Dim PefRange As Excel.Range
Try
WRITE_TO_EXCEL = 0
If worksheet_index = 1 Then
objxl = New Excel.Application
objxl.Visible = False
objwbs = objxl.Workbooks
objwb = objwbs.Add
With objwb
.Sheets.Add()
End With
End If
objws = CType(objwb.Worksheets(worksheet_index), Excel.Worksheet)
objws.Name = dt.TableName
With objws
.Cells.NumberFormat = "@"
End With
If includeheader Then
For Each col In dt.Columns
colindex += 1
objws.Cells(1, colindex) = col.ColumnName
Next
rowindex = 1
Else
rowindex = 0
End If
For Each mrow In dt.Rows
rowindex += 1
colindex = 0
For Each col In dt.Columns
colindex += 1
objws.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()
'APPLY FORMATTING
PefRange = objwb.Application.Range("A1:AA1")
With PefRange.EntireRow
.Font.Bold = True
.Font.Size = 12
.Interior.ColorIndex = 6
.Interior.Pattern = Excel.XlPattern.xlPatternSolid
.Font.Name = "Rockwell"
.Font.Underline = True
End With
Next
Next
With objws
.Cells.EntireColumn.AutoFit()
End With
objxl.DisplayAlerts = False
Catch ex As Exception
objwb.Close()
End
End Try
End Function