Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving the values of Excel check boxes in .NET
I have an Excel spreadsheet which contains multiple un-grouped check boxes
(from the Forms toolbar) which are not linked to any particular cell(s). I want to read their values in .NET code. For this I'm using 'Microsoft Office Interop Excel' and 'Microsoft Vbe Interop Forms' libraries. This is the code I'm using: String excelFile = "C:\\FileName.xls"; Excel.Application estimate = new Excel.Application(); Excel.Worksheet workSheet = new Excel.Worksheet(); estimate.Workbooks.Open(excelFile, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); workSheet = (Excel.Worksheet)estimate.Worksheets.get_Item("Wor ksheetName"); Excel.CheckBox cb = (Excel.CheckBox)workSheet.Shapes.Item("CheckBoxNam e").OLEFormat.Object; cb.Value returns the value of the check box (the value is -4146 if the check box is unchecked, 1 if it is checked and null if it is mixed). At some point in time I was able to see the names of all the check boxes in my worksheet (they were named "Check Box 1", "Check Box 2" and so on). But now I see that all the check boxes have the same name "CheckBox" which is very strange. This is making it impossible for me to retrieve the values of the check boxes by specifying the check box name. I know I can use the index also but it poses a similar problem - 'how do I know the index of each check box?'. Coming back to the name issue, is there any way I can see the unique names of all my check boxes again? Is there any mode which I have to use to be able to see the unique names? What am I missing here? Any help would be greatly appreciated. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving the values of Excel check boxes in .NET
Thanks for your reply, Peter. I used the following C# code to verify the
names of the check boxes: textBox1.Text = ""; foreach (Excel.Shape shp in workSheet.Shapes) { if (shp.Type.ToString() == "msoFormControl" && shp.FormControlType.ToString() == "xlCheckBox") { Excel.CheckBox cb = (Excel.CheckBox)shp.OLEFormat.Object; string output = cb.Index.ToString() + "\t" + shp.Name.ToString() + "\t" + cb.Text.ToString() + "\t" + cb.Value.ToString() + "\r\n"; textBox1.Text += output; } } With this I get the index, name, text and value for each check box in my worksheet, but all the names returned are "CheckBox". My spreadsheets contain some survey results which my client conducted. I'm supposed to enter the contents of these spreadsheets into a database. Modifying the names of all check boxes is clearly out of scope of my work and, I think, is also error prone. Is there any other way out? Thanks again. "Peter T" wrote: But now I see that all the check boxes have the same name "CheckBox" which is very strange. Indeed very strange. Try a bit of VBA to verify their names (in this case loop by index rather than For Each) Sub test() Dim i As Long Dim ws As Worksheet Dim sh As Shape Dim cb As CheckBox Set ws = ActiveSheet For i = 1 To ws.CheckBoxes.Count With ws.CheckBoxes(i) Debug.Print i, .Name, .Value, _ .TopLeftCell.Address(0, 0) End With Next Debug.Print For i = 1 To ws.Shapes.Count Set sh = ws.Shapes(i) If sh.Type = msoFormControl Then If sh.FormControlType = xlCheckBox Then Debug.Print i, sh.Name, sh.OLEFormat.Object.Value, _ sh.TopLeftCell.Address(0, 0) End If End If Next End Sub Normally you should see similar results in both loops although the index numbers i may be different if the sheet contains other shapes besides checkboxes. If all the names are the same, theoretically possible but unusual, use VBA to rename them. Use some logical naming convention looping the Checkboxes collection. FWIW you might come across xlOn & xlOff as referring to 1 & -4146 respectively. Regards, Peter T "Amit Kathuria" <Amit wrote in message ... I have an Excel spreadsheet which contains multiple un-grouped check boxes (from the Forms toolbar) which are not linked to any particular cell(s). I want to read their values in .NET code. For this I'm using 'Microsoft Office Interop Excel' and 'Microsoft Vbe Interop Forms' libraries. This is the code I'm using: String excelFile = "C:\\FileName.xls"; Excel.Application estimate = new Excel.Application(); Excel.Worksheet workSheet = new Excel.Worksheet(); estimate.Workbooks.Open(excelFile, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); workSheet = (Excel.Worksheet)estimate.Worksheets.get_Item("Wor ksheetName"); Excel.CheckBox cb = (Excel.CheckBox)workSheet.Shapes.Item("CheckBoxNam e").OLEFormat.Object; cb.Value returns the value of the check box (the value is -4146 if the check box is unchecked, 1 if it is checked and null if it is mixed). At some point in time I was able to see the names of all the check boxes in my worksheet (they were named "Check Box 1", "Check Box 2" and so on). But now I see that all the check boxes have the same name "CheckBox" which is very strange. This is making it impossible for me to retrieve the values of the check boxes by specifying the check box name. I know I can use the index also but it poses a similar problem - 'how do I know the index of each check box?'. Coming back to the name issue, is there any way I can see the unique names of all my check boxes again? Is there any mode which I have to use to be able to see the unique names? What am I missing here? Any help would be greatly appreciated. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving the values of Excel check boxes in .NET
You might find this line will error if the shape is not a forms control
if (shp.Type.ToString() == "msoFormControl" && shp.FormControlType.ToString() == "xlCheckBox") That's why in the VBA example I suggested If sh.Type = msoFormControl Then If sh.FormControlType = xlCheckBox Then Did you actually try the VBA I posted, it will only take you a few seconds. There's a very small possibility you might get different results looping by index rather than For each. Modifying the names of all check boxes is clearly out of scope of my work and, I think, is also error prone. Is there any other way out? Why not explain to your client the problem and suggest you rename them. I don't see what's error prone about doing that. Seems like a sensible and easy thing to do. Alternatively you will need some logic to uniquely identify your checkboxes, eg, location or caption. Of course either of these may accidentally change. Index is definitely not a reliable way to permanently identify them. In your position I'd want to get to find out why the chekboxes all ended up with the same name, quite difficult to achieve. Normally the only way to do that is: - rename the shape from its default original - group it with other shape(s) - copy the group When you ungroup the copied group, any previously renamed shapes will be duplicated. Try looking at the AlternativeText property. If the are all the same, eg "Check Box 1", that'd be a strong indicator that someone has done what I described above, a bit odd though. Regards, Peter T "Amit Kathuria" wrote in message ... Thanks for your reply, Peter. I used the following C# code to verify the names of the check boxes: textBox1.Text = ""; foreach (Excel.Shape shp in workSheet.Shapes) { if (shp.Type.ToString() == "msoFormControl" && shp.FormControlType.ToString() == "xlCheckBox") { Excel.CheckBox cb = (Excel.CheckBox)shp.OLEFormat.Object; string output = cb.Index.ToString() + "\t" + shp.Name.ToString() + "\t" + cb.Text.ToString() + "\t" + cb.Value.ToString() + "\r\n"; textBox1.Text += output; } } With this I get the index, name, text and value for each check box in my worksheet, but all the names returned are "CheckBox". My spreadsheets contain some survey results which my client conducted. I'm supposed to enter the contents of these spreadsheets into a database. Modifying the names of all check boxes is clearly out of scope of my work and, I think, is also error prone. Is there any other way out? Thanks again. "Peter T" wrote: But now I see that all the check boxes have the same name "CheckBox" which is very strange. Indeed very strange. Try a bit of VBA to verify their names (in this case loop by index rather than For Each) Sub test() Dim i As Long Dim ws As Worksheet Dim sh As Shape Dim cb As CheckBox Set ws = ActiveSheet For i = 1 To ws.CheckBoxes.Count With ws.CheckBoxes(i) Debug.Print i, .Name, .Value, _ .TopLeftCell.Address(0, 0) End With Next Debug.Print For i = 1 To ws.Shapes.Count Set sh = ws.Shapes(i) If sh.Type = msoFormControl Then If sh.FormControlType = xlCheckBox Then Debug.Print i, sh.Name, sh.OLEFormat.Object.Value, _ sh.TopLeftCell.Address(0, 0) End If End If Next End Sub Normally you should see similar results in both loops although the index numbers i may be different if the sheet contains other shapes besides checkboxes. If all the names are the same, theoretically possible but unusual, use VBA to rename them. Use some logical naming convention looping the Checkboxes collection. FWIW you might come across xlOn & xlOff as referring to 1 & -4146 respectively. Regards, Peter T "Amit Kathuria" <Amit wrote in message ... I have an Excel spreadsheet which contains multiple un-grouped check boxes (from the Forms toolbar) which are not linked to any particular cell(s). I want to read their values in .NET code. For this I'm using 'Microsoft Office Interop Excel' and 'Microsoft Vbe Interop Forms' libraries. This is the code I'm using: String excelFile = "C:\\FileName.xls"; Excel.Application estimate = new Excel.Application(); Excel.Worksheet workSheet = new Excel.Worksheet(); estimate.Workbooks.Open(excelFile, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); workSheet = (Excel.Worksheet)estimate.Worksheets.get_Item("Wor ksheetName"); Excel.CheckBox cb = (Excel.CheckBox)workSheet.Shapes.Item("CheckBoxNam e").OLEFormat.Object; cb.Value returns the value of the check box (the value is -4146 if the check box is unchecked, 1 if it is checked and null if it is mixed). At some point in time I was able to see the names of all the check boxes in my worksheet (they were named "Check Box 1", "Check Box 2" and so on). But now I see that all the check boxes have the same name "CheckBox" which is very strange. This is making it impossible for me to retrieve the values of the check boxes by specifying the check box name. I know I can use the index also but it poses a similar problem - 'how do I know the index of each check box?'. Coming back to the name issue, is there any way I can see the unique names of all my check boxes again? Is there any mode which I have to use to be able to see the unique names? What am I missing here? Any help would be greatly appreciated. Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving the values of Excel check boxes in .NET
We have a 2003 spreadsheet with a check box. We are trying to retrieve
the values from this through a .NET 2008 windows application. 1. We have included Office.Interop.Excel and VBE.Interop.Forms in the code. 2. We have added the checkbox from the toolbar and not through code. 3. We tried Excel.CheckBox cb = (Excel.CheckBox)workSheet.Shapes.Item("CheckBox4") .OLEFormat.Object; and got Specified Cast is not Valid 4. We tried Forms.CheckBox cb = (Forms.CheckBox)workSheet.Shapes.Item("CheckBox4") .OLEFormat.Object; and got Specified Cast is not Valid Please help. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Boxes In Excel | Excel Discussion (Misc queries) | |||
Check Boxes In Excel | Excel Discussion (Misc queries) | |||
Help with Comparing values and retrieving values in Excel!!!!!! | Excel Worksheet Functions | |||
How do i set up check boxes in excel? | New Users to Excel | |||
Using Check boxes in Excel | Excel Worksheet Functions |