Excel problem: Cant hide column.



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
rowindex = 1
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) =

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

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

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.RemoveAt( column index) .

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

-sachin salgarkar

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question