Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel problem: Cant hide column.
Thanks for the quick response.
What is the xlToLeft? A parameter? a string? regards, Sorcerdon |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
How do I hide text beyond the last column in Excel? | Excel Discussion (Misc queries) | |||
Check COlumn - Excel VBA | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel 97 Column & Row Header Problem | Excel Discussion (Misc queries) |