Automatically updating listbox in excel cell from VB .NET

[jnoble123]

I am attempting to create a bridge application between two time keeping systems using VB .NET

Basically I am performing the following steps:

1) Download time report from one system as an excel file
2) Parse the excel report file. Match up project and tasks codes (different in both systems) from an excel database.
3) Update an upload spreadsheet supplied by the 2nd systems vendor.

This upload spreadsheet contains project codes, task codes, time spent etc. I can update all of this information successfully using

ewsUploadData.Cells(iCount2, 3) = sTDProj

or

ewsUploadData.Range(sTemp2).Value = sTemp5

The remaining problem I have has to do with one particular set of cells. These cells appear to have a dropdown list in them. This list goes across two cells in the spreadsheet.

If I manually use keyboard controls to highlight the cell, the following appears in the cell:

INDEX(Types!C$2:C$3,Cell Links!A2)

The second cell covered by the same dropdown reads:

INDEX(Types!B$2:B$3,Cell Links!A2)

The cells are listed as protected as well.

Any ideas on how I would go about updating this type of cell from VB.NET?

Your help will be greatly appreciated. Ive been searching google and other web sites for a couple of days now.

~Jamie N

 

[herilane]

A cell is very unlikely have both a formula and a dropdown box. Its more likely that the dropdown is a separate combobox object which is positioned over the cell.

The INDEX formula is a lookup formula. INDEX(Types!C$2:C$3,Cell Links!A2) for example would grab the value of Cell Links!A2, and return the corresponding value from Types!C$2:C$3. If Cell Links!A2=1, the formula returns whatever is in Types!C$2; if Cell Links!A2=2, it returns whatever is in Types!C$3.

Perhaps the dropdown boxes affect Cell Links!A2, so they are a way to indirectly choose the values for these 2 cells?