CODEWITHSUNDEEP
c#.netexcelvbaclosedxml
Pecheneg
Pecheneg

Reputation: 808

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

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.

Upvotes: 9

Views: 2036

Answers (5)

Mukul Varshney
Mukul Varshney

Reputation: 3141

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

Upvotes: 3

Mukul Varshney
Mukul Varshney

Reputation: 3141

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

Upvotes: 1

ThunderFrame
ThunderFrame

Reputation: 9461

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.

Upvotes: 1

user2226755
user2226755

Reputation: 13173

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

Like this :

enter image description here

See :

enter image description here

Upvotes: 0

user2226755
user2226755

Reputation: 13173

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

enter image description here

Upvotes: 1

Related Questions