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.

Turkish

Answer 1

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;
        }

Endpaper

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. – Turkish

Try using xlSheet.ListObjects, .ChartObjects. Refer msdn.microsoft.com/en-us/library/… – Endpaper

Answer 2

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

Mopup

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. – Turkish

Answer 3

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.

Noreen

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. – Turkish

I found a library in this URL and now trying to bind it yo my code: github.com/gHowl/gHowlComponents/blob/master/gHowl/gHowl/… That's how I reach the worksheet: XLWorkbook workbook = new XLWorkbook(inputStream); IXLWorksheet wsAcpMerchant = workbook.Worksheet(1); – Turkish

I'll have a closer look when I have more time, but you might also want to look at this approach using the Open XML SDK. – Noreen

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. – Turkish

Answer 4

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

Endpaper

Which library is this? – Turkish

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 – Ptyalism

This was part of excel macro. I have written another answer using C# which access the checkbox and textbox value. – Endpaper

Answer 5

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

Like this :

See :

Mopup

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