Reach ActiveX or Form objects (textbox) from codebehind in an Excel document

Question

There are several textboxes in an excel file as ActiveX objects and I want to reach them from codebehind.

I am using ClosedXML for other fields, but I am open for other suggestions.

score 3 · Accepted Answer · answered Sep 14 '16 at 09:16

For accessing OLE objects from C#, add reference to Microsoft Forms 2.0 object library. You can iterate through the controls for your desired checkbox and textbox. Enjoy !

using Excel = Microsoft.Office.Interop.Excel;
using VBE = Microsoft.Vbe.Interop.Forms;

private static void ExcelOperation(string xlFileName)
        {
            var xlApp = new Excel.Application();
            var xlWorkbook = xlApp.Workbooks.Open(xlFileName);
            var xlSheet = xlWorkbook.Worksheets["your_sheet_Name"] as Excel.Worksheet;

            try
            {
                Excel.OLEObjects oleObjects = xlSheet.OLEObjects() as Excel.OLEObjects;
                foreach (Excel.OLEObject item in oleObjects)
                {                   
                    if (item.progID == "Forms.TextBox.1")
                    {
                        VBE.TextBox xlTB = item.Object as VBE.TextBox;
                        Console.WriteLine("Name: " + item.Name);
                        Console.WriteLine("Text: " + xlTB.Text);
                        Console.WriteLine("Value: " + xlTB.get_Value());
                        Marshal.ReleaseComObject(xlTB); xlTB = null;
                    }
                    else if (item.progID == "Forms.CheckBox.1")
                    {
                        VBE.CheckBox xlCB = item.Object as VBE.CheckBox;
                        Console.WriteLine("checkbox: " + item.Name);
                        Console.WriteLine("Value: " + xlCB.get_Value());
                        Marshal.ReleaseComObject(xlCB); xlCB = null;
                    }                    

                }

                Marshal.ReleaseComObject(oleObjects); oleObjects = null;
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Marshal.ReleaseComObject(xlSheet); xlSheet = null;
            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook); xlWorkbook = null;
            Marshal.ReleaseComObject(xlApp); xlApp = null;
        }

Thanks! That did the trick. Found out that my checkboxes are "Form" objects though. Any Idea how to reach them too? I got PublishObject, ListObject, ChartObject, UsedObject in the library. — Pecheneg, Sep 14 '16 at 09:50
Try using xlSheet.ListObjects, .ChartObjects. Refer https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.controls.aspx — Mukul Varshney, Sep 14 '16 at 11:08

user2226755 · Answer 2 · 2016-09-10T10:58:42.717

You can make file in VBA (in %appdata% folder), and save textboxes value in this file (by example, .ini file).

And after that open file in C#.

VBA (.xlsm file)

'               PtrSafe for 64bit (for 32bit, remove it)
Private Declare PtrSafe Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" _
    (ByVal lpApplicationName As String, ByVal lpKeyName As Any, _
    ByVal lpString As Any, ByVal lpFileName As String) As Long

Private Sub TextBox1_Change()
    WritePrivateProfileString "Page1", "TextBox1", TextBox1.Text, "C:\Users\HS\Desktop\exceltab.ini"
End Sub

C:\Users\HS\Desktop\exceltab.ini

[Page1]
TextBox1=ok

Hi, I am not trying to create the checkbox and change the value of it. I am trying to reach a textbox which is already there. — Pecheneg, Sep 12 '16 at 08:12

score 1 · Answer 3 · answered Sep 11 '16 at 10:30

1

If you're using ClosedXML, you should take a look at XLWSContentManager

As per Microsoft:

ActiveX controls are represented by OLEObject objects in the OLEObjects collection (all OLEObject objects are also in the Shapes collection)

So, using ClosedXML, you need to use XLWSContents.OleObjects, but if the controls aren't ActiveX Controls, and are instead the built-in Excel Form controls, you'll need to use XLWSContents.Controls.

But note the MS documentation - ClosedXML's implementation may require you to also check the Shapes.

answered Sep 11 '16 at 10:30

ThunderFrame

9,352
2
29
60

Thanks for this, it looks like exactly what I am looking for. Would you give me more details please? I am also searching for the libraries but somehow my .dll doesn't have XLWSContents class. – Pecheneg Sep 12 '16 at 08:21
I found a library in this URL and now trying to bind it yo my code: https://github.com/gHowl/gHowlComponents/blob/master/gHowl/gHowl/ClosedXML/Excel/XLWSContentManager.cs That's how I reach the worksheet: XLWorkbook workbook = new XLWorkbook(inputStream); IXLWorksheet wsAcpMerchant = workbook.Worksheet(1); – Pecheneg Sep 12 '16 at 08:41
1

I'll have a closer look when I have more time, but you might also want to look at this [approach](http://stackoverflow.com/a/13377755/5757159) using the Open XML SDK. – ThunderFrame Sep 12 '16 at 20:44
Thanks. I tried the approach, unfortunately Excel file wasn't compatible with "WordprocessingDocument" class. The library look very hopeful though, I wish ClosedXML has such libraries. – Pecheneg Sep 13 '16 at 08:34

score 1 · Answer 4 · answered Sep 14 '16 at 05:13

1

You can iterate all the OLE objects as below and identify the textbox you need by textbox name or textbox Text. Instead of Me, you can use worksheet refrence from outside the workbook or any code.

Private Sub GetActiveXControls()

  For Each Item In Me.OLEObjects
    'Debug.Print Item.Name
    If TypeName(Item.Object) = "TextBox" Then
      Debug.Print "text = " & Item.Object.text
    End If
  Next

End Sub

answered Sep 14 '16 at 05:13

Mukul Varshney

3,131
1
12
19

Which library is this? – Pecheneg Sep 14 '16 at 08:10
I think this is part of 'Microsoft Excel xx.x Object Library'. Me.OLEObjects is a collection of all OLE objects in your worksheet. Similar collection exists for FORMs as well – Zac Sep 14 '16 at 08:37
This was part of excel macro. I have written another answer using C# which access the checkbox and textbox value. – Mukul Varshney Sep 14 '16 at 09:19

score 0 · Answer 5 · answered Sep 10 '16 at 11:04

0

If you want use ClosedXML you can link textarea & cell with LinkedCell properties.

Like this :

See :

answered Sep 10 '16 at 11:04

user2226755

12,494
5
50
73

That looks good, but I am not trying to create the checkbox and change the value of it. I am trying to reach a textbox which is already there. Would you do the same with a checkbox? – Pecheneg Sep 12 '16 at 08:13

Reach ActiveX or Form objects (textbox) from codebehind in an Excel document

5 Answers5

VBA (.xlsm file)

C:\Users\HS\Desktop\exceltab.ini

Linked