Member Avatar for Gus_19

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
  • 273 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

Two things are happening that explain why only the first sheet shows the header formatting: the Range used for formatting is being resolved at the Application/active-sheet level, and the formatting is applied repeatedly inside the inner per-cell loop (slow and error-prone). Qualify the range with the worksheet you are writing to, apply formatting once after the header row is written, and make sure the target sheet actually exists before you try to format it.

Apply formatting for the header row like this (run this after you write the header cells and after ensuring the sheet exists):

Dim lastCol As Integer = dt.Columns.Count
Dim headerRange As Excel.Range = objws.Range(objws.Cells(1, 1), objws.Cells(1, lastCol))

With headerRange
    .Font.Bold = True
    .Font.Size = 12
    .Font.Name = "Rockwell"
    .Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
    .Interior.ColorIndex = 6
    .Interior.Pattern = Excel.XlPattern.xlPatternSolid
End With

Also, ensure a worksheet exists for the index you pass in before you reference it (add sheets as needed), and avoid calling Range via the Application object. As suggested, looping through Worksheets or explicitly creating the required sheet(s) before writing/formatting is a robust approach.

Other practical tips and gotchas:

  • Move the formatting call outside inner loops (do it once per sheet).
  • When naming a sheet from dt.TableName, sanitize/truncate names (max 31 chars; no [ ] : * ? / \ ) and handle duplicate names.
  • Use objws.Columns.AutoFit or call AutoFit after formatting, not inside cell loops.
  • Clean up COM objects (Close, Quit, Marshal.ReleaseComObject, GC.Collect/WaitForPendingFinalizers) to avoid orphan Excel processes.

Following the above will make the formatting target each worksheet reliably and will dramatically improve performance.

Member Avatar for Reverend Jim

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.