Retrieving the values of Excel check boxes in .NET

[Amit Kathuria]

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("WorksheetName");
Excel.CheckBox cb =
(Excel.CheckBox)workSheet.Shapes.Item("CheckBoxName").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.

 

[Peter T]

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]

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]

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

 

[samik banerjee]

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