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

Excel problem: Cant hide column.
 

Haven't played with .NET, but it should work anyway.

objws.Columns("D:D").Delete Shift:=xlToLeft

If I understand you correctly you want to first build the table and
then delete a column. If so, this line should go after all the loops.
You can probably put it befo
objxl.DisplayAlerts = False

Does this help?
Kostis Vezerides

Sorcerdon wrote:
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?

Excel problem: Cant hide column.
 

Thanks for the quick response.

What is the xlToLeft?
A parameter? a string?

regards,
Sorcerdon

Excel problem: Cant hide column.
 

BTW,

It still doesnt work.
I am working in VB.net
it doesnt like the line

objws.Columns("D:D").Delete Shift:=xlToLeft

it says Shift:=xlToLeft is not declared.
Meaning, it doesnt recognize it.

Excel problem: Cant hide column.
 

Your a genius!
I was missing the proper range format.

I used the following lines to achive what i wanted:

To get the "D:D" stringi used the following:
rangeArray(colindex) = objws.Cells(1, colindex).EntireColumn.Address()

Dim myStr As String
For Each myStr In rangeArray
If myStr < "" Then
objws.Columns(myStr).Hidden = True
End If
Next

And this to finish it off.