av197


I am building a Visual Basic program that has to read an Excel SpreadSheet and get the values of unprotected cells as well as the values of any control (placed anywhere from the spreadsheet creator), use them somewhere else and vice versa, i.e. open a preconstructed Excel spreadsheet and place values in cells but also in the controls.

I've found more than one ways to read and modify the cells. I've found NO way though to read the controls (calendar, combobox, textbox, checkbox) values. I know that the obvious solution is to link each control to a cell ! The problem is that I am not the creator of the Excel file and there is no way to "force" the creator to create the links.

I've read about the Visual Studio Office Tools capabilities but unfortunately they talk about Excel files created within the Studio and the Controls they refer to are Windows.Forms Controls created from the Studio platform.

The controls I'm interested in accessing, reading and modifying, are the ones placed in the Excel Spreadsheet by somebody who just used Excel to create a spreadsheet.

I would highly appreciate if anybody had a solution to suggest.
Thanks in advance

av



Re: How to read the value of a non-linked to a cell control from an Excel Sheet?

Bruno Yu - MSFT


av197,

According to your question on reading values from the Excel SpreadSheet cell control, I would like to provide you the suggestions as follows:

1. You can consider to use Excel Automation to perform actions such as creating a new workbook, adding data to the workbook, or creating charts. With Excel and other Microsoft Office applications, virtually all of the actions that you can perform manually through the user interface can also be performed programmatically by using Automation. The following KB article shows you the way:

How to automate Microsoft Excel from Visual Basic .NET

2. The following thread can provide you some idea on the issue:

Reading Excel Spreadsheets

I'm reading the Excel spreadsheet using the ODBC driver but I've noticed that some cells are reading as Nulls even though there's data in the cells. IOW, when I view the file in Excel, there's data. When I read the cell thru ADO.NET, I get a System.InvalidCastException 'Cast from type 'DBNull' to type 'String' is not valid.' exception. This doesn't happen for all cells, only some. The only difference between the ones that work and the ones that don't that I've noticed is that in Excel the ones that work are stored as strings and the cells that don't are stored as numbers. But it should work either way. I'm using VS.NET 2003, Excel 2003 and the ODBC driver and ODBC ADO.NET data provider.

3. For some examples, you can consider to download some third-party application that can help you to read the Excel cell control. I would like to provide you the following example:

Using Excel For Test Data

3. If you have related any questions, you could also post the thread in Visual Basic for Applications forum. There are more exports in solving this kind of problem and I believe you can get the further support in that forum.

Hope that can provide you some idea and thanks again for your question.







Re: How to read the value of a non-linked to a cell control from an Excel Sheet?

av197

Bruno,

thanks for your answer. I've checked the links you suggested and had tested the solution you suggest. All that works fine as data stored in cells is concerned.

However, the problem is the controls that the Spreadsheet creator can use from the two available toolbars, namely: Control ToolBox and Forms.

On another forum that I posted the question, somebody suggested to try the following:

- Start recording Macros
- Create controls(a textbox, a combobox, a calendar) using Toolbar: Control ToolBox
- Create controls(a combobox) using Toolbar: Forms
- Stop Recording Macros
- Check the VBA code produced

Using the Control Toolbox controls:
--------------------------------------------------


Code Block

Sub Macro1()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=277.5, Top:=125.25, Width:=132, Height:= _
18).Select

ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar.7", Link:=False, _
DisplayAsIcon:=False, Left:=285, Top:=168, Width:=255.75, Height:= _
157.5).Select

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=120, Top:=72.75, Width:=108.75, Height:= _
15.75).Select
End


Using the Forms controls:
-------------------------------------
Code Block

Sub Macro1()

ActiveSheet.DropDowns.Add(147.75, 36.75, 187.5, 24).Select
With Selection

.ListFillRange = "$E$7 : $E$14"
.LinkedCell = "$G$11"
.DropDownLines = 8
.Display3DShading = False
End With
End


This means that the arrays I have to acces from a VB .NET Application are the folloing:

xlSheet.OLEObjects, xlSheet.DropDowns, xlSheet.ListBoxes etc.

If the control in question is one of a single value, I can access it as follows:

MsgBox(xlSht.OLEObjects(i).object.Value)
MsgBox(xlSht.DropDowns(i).object.Value)

All the above solved my intial problem.

What remains unsolved is the case of a ListBox where the .Value attribute has no sense when MultiSelection is allowed.
I cannot figure out how to access the array of items. The main problem is that whatever casting I have tried fails, and the error is that you cannot cast a System.__ComObject to a type of ListBox (or whatever else type).

I would appreciate it if you could help me on that.

Thanks again

av






Re: How to read the value of a non-linked to a cell control from an Excel Sheet?

Bruno Yu - MSFT

av197,

It is better to post the question in Visual Basic for Applications forum. I have moved the thread in VBA forum. There are more exports on VBA development, I believe you can get the further support and the satisfying answer in this forum.

Thanks again for your question and reply.






Re: How to read the value of a non-linked to a cell control from an Excel Sheet?

Cringing Dragon

For a ListBox using MultiSelection, you need to use the Selected property instead of the Value property.

ListBox1.Selected

returns the array of selected values in ListBox1.

ListBox1.Selected(1)

returns the first selected value.






Re: How to read the value of a non-linked to a cell control from an Excel Sheet?

av197

Cringing Dragon,

thanks a lot. Your answer solves the problem for the ListBox that comes out from the Forms Toolbar.

The .Selected array actually is an array of Length = to the number of items on the list and is an array of Booleans, each of which determines whether the corresponding List Item is selected or not. To count the items I used .Selected.Lenght. The .Count property produces an error.


However, the problem remains for the ListBox that comes out of the Control ToolBox Toolbar. The .Selected property produces an error there. If you manage to find a solution for that as well, please let me know.

Thanks a lot again.

av


P.S. Bruno, thanks for moving the thread to a more appropriate section, and I apologise for not having done it myself.