C# Retrieve Excel TextBox Text

[granttrimble]

Hi

Can anybody please assist me with this problem. I'm trying to retrieve
the values from text boxes via C# (2005) in an Excel (2003) spread
sheet. I have tried casting the OLEFormat.Object to an MSForms.Text box
(2.0) which was unsuccessfull:


Excel.ApplicationClass excelApplication = null;
Excel.Workbook excelWorkbook = null;
Excel.Worksheet excelWorkSheet = null;

try
{
excelApplication = new Excel.ApplicationClass();
excelWorkbook =
excelApplication.Workbooks.Open(FileName,
Type.Missing,Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing);

string strSheet = "Dealer Job Card";
excelWorkSheet =
(Excel.Worksheet)excelWorkbook.Worksheets.get_Item(strSheet);

string strTextBoxName = "txtAccountNo";

Excel.Shape oShapeName =
excelWorkSheet.Shapes.Item(strTextBoxName);

MSForms.TextBox excelTextBox = (MSForms.TextBox)

oShapeName.OLEFormat.Object;

string strText = excelTextBox.Text;

I get this exception:

Unable to cast COM object of type 'System.__ComObject' to interface
type 'Microsoft.Vbe.Interop.Forms.TextBox'. This operation failed
because the QueryInterface call on the COM component for the interface
with IID '{8BD21D13-EC42-11CE-9E0D-00AA006002F3}' failed due to the
following error: No such interface supported (Exception from HRESULT:
0x80004002 (E_NOINTERFACE)).

I have also tried this:

Excel.ApplicationClass excelApplication = null;
Excel.Workbook excelWorkbook = null;
Excel.Worksheet excelWorkSheet = null;

excelApplication = new Excel.ApplicationClass();
excelWorkbook = excelApplication.Workbooks.Open(FileName,
Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing);

string strSheet = "Dealer Job Card";
excelWorkSheet =
(Excel.Worksheet)excelWorkbook.Worksheets.get_Item(strSheet);

string strTextBoxName = "txtAccountNo";

Excel.Shape oShapeName =
excelWorkSheet.Shapes.Item(strTextBoxName);

Type t = oShapeName.OLEFormat.Object.GetType();

string strText = t.InvokeMember("Text",
BindingFlags.GetProperty, null,

oShapeName.OLEFormat.Object, null).ToString();


which doesn't want to work either. I get the following exception:

Unknown name. (Exception from HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME))

 

[Cindy M.]

Can anybody please assist me with this problem. I'm trying to retrieve

the values from text boxes via C# (2005) in an Excel (2003) spread
sheet.

There is more than one kind of "text box" in the Office world. How were
these text boxes inserted into the spreadsheet? Using the Drawing tools?
The Control Toolbox? The Forms tools?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[granttrimble]

Hi Cindy

The Control Toolbox

 

[granttrimble]

Thanks for pointing me in the right direction Cindy. Here is the
solution:

Excel.ApplicationClass excelApplication = null;
Excel.Workbook excelWorkbook = null;
Excel.Worksheet excelWorkSheet = null;

try
{
excelApplication = new Excel.ApplicationClass();
excelWorkbook =
excelApplication.Workbooks.Open(FileName,

Type.Missing,

Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing,

Type.Missing);


string strSheet = "Dealer Job Card";
excelWorkSheet =
(Excel.Worksheet)excelWorkbook.Worksheets.get_Item(strSheet);

string strTextBoxName = "txtAccountNo";

Excel.Shape excelShape =
excelWorkSheet.Shapes.Item(strTextBoxName);

Excel.OLEObject excelOLEObject =
(Excel.OLEObject)excelShape.OLEFormat.Object;

string strText =
excelOLEObject.Object.GetType().InvokeMember("Text",
BindingFlags.GetProperty, null, excelOLEObject.Object, null).ToString();

 

[Cindy M.]

Thanks for pointing me in the right direction Cindy.Glad you found it Life suddenly got very busy this week
and I wasn't able to follow-up.

Cindy Meister