0

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
2
Contributors
1
Reply
2
Views
4 Years
Discussion Span
Last Post by Reverend Jim
0

If you want to do something to each worksheet then the typical method is to enumerate over the collection as follows:

For Each sheet As Excel.WorkSheet In onjwb(1).WorkSheets
    'apply formatting
Next
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.