Excel problem: Cant hide column.

  • Thread starter Thread starter Sorcerdon
  • Start date Start date
S

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?
 
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
 
Back
Top