Excel problem: Cant hide column.

10 Oct 2006 4:39 PM

Sorcerdon

Hello experts,

I am currently opening an excel file from a VB.net application.
The excel spreadsheet gets all its data from a Dataset.
I need to hide a column form the results.
I dont want the users to have access to that perticular column in the
excel spreadsheet.

Here is my current code:

Dim dt As DataTable = dsSql.Tables("TableName")
Dim col As DataColumn
Dim mrow As DataRow
Dim objxl As Excel.Application
Dim objwbs As Excel.Workbooks
Dim objwb As Excel.Workbook
Dim objws As Excel.Worksheet
Dim colindex As Integer
Dim rowindex As Integer

        objxl = New Excel.Application
        objwbs = objxl.Workbooks
        objwb = objwbs.Add
        objws = CType(objwb.Worksheets(1), Excel.Worksheet)

        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()
             Next
        Next

        objxl.DisplayAlerts = False 'Makes overwrites automatic so that
a prompt to overwrite doesnt pop up
        objwb.SaveAs(sFileName)
        objwb.Close()
        objxl.DisplayAlerts = True 'Makes overwrites non-automatic so
that a prompt to overwrite does pop up
        objxl.Quit()
        objws = Nothing
        objwb = Nothing
        objwbs = Nothing
        objxl = Nothing

What is the code to delete ONE entire column and where do I place it?

11 Oct 2006 3:35 PM

Sachin Salgarkar

There are 2 ways to do it.
You can either remove the column from the table before you export it to the
Excel sheet.

dt.Columns.Remove("Columnname")
OR
dt.Columns.RemoveAt( column index) .

The other way is to ignore the column in the For Each loops.

-sachin salgarkar

Show quote

"Sorcerdon" <sorcer***@gmail.com> wrote in message
news:1160498343.330398.127210@b28g2000cwb.googlegroups.com...
> Hello experts,
>
> I am currently opening an excel file from a VB.net application.
> The excel spreadsheet gets all its data from a Dataset.
> I need to hide a column form the results.
> I dont want the users to have access to that perticular column in the
> excel spreadsheet.
>
> Here is my current code:
>
> Dim dt As DataTable = dsSql.Tables("TableName")
> Dim col As DataColumn
> Dim mrow As DataRow
> Dim objxl As Excel.Application
> Dim objwbs As Excel.Workbooks
> Dim objwb As Excel.Workbook
> Dim objws As Excel.Worksheet
> Dim colindex As Integer
> Dim rowindex As Integer
>
>
>        objxl = New Excel.Application
>        objwbs = objxl.Workbooks
>        objwb = objwbs.Add
>        objws = CType(objwb.Worksheets(1), Excel.Worksheet)
>
>        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()
>             Next
>        Next
>
>        objxl.DisplayAlerts = False 'Makes overwrites automatic so that
> a prompt to overwrite doesnt pop up
>        objwb.SaveAs(sFileName)
>        objwb.Close()
>        objxl.DisplayAlerts = True 'Makes overwrites non-automatic so
> that a prompt to overwrite does pop up
>        objxl.Quit()
>        objws = Nothing
>        objwb = Nothing
>        objwbs = Nothing
>        objxl = Nothing
>
>
>
>
> What is the code to delete ONE entire column and where do I place it?
>