Return ActiveX or Form objects (text box) from code in an Excel document

Question

There are several text fields in the excel file as ActiveX objects, and I want to get them from codebehind.

I use ClosedXML for other fields, but I am open to other suggestions.

Answer 1

To access OLE objects from C # add a link to the Microsoft Forms 2.0 object library. You can iterate through the controls for the checkbox and text box you want. Enjoy it!

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

Answer 2

You can make a file in VBA (in the% appdata% folder) and save the value of the text fields in this file ( for example, .ini file ).

And after that open the file in C #.

VBA file (.xlsm)

 ' 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 

Answer 3

If you are using ClosedXML, you should take a look at XLWSContentManager

By Microsoft :

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

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

But look at the MS documentation. ClosedXML implementation may also require validation of shapes.

Answer 4

You can iterate through all OLE objects as shown below and define the text field you need using a text field or a Text text field. Instead of me you can use the rejection sheet from an external book 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 

Answer 5

If you want to use ClosedXML , you can associate textarea and cell with LinkedCell properties.

Like this:

See: