Excel - Combobox data export problem

[datatransfer]

Hello all,
We have a situation where we need to transfer data from an Excel
spreadsheet to a SQL Server database. The excel spreadhseet has 25
fields of which 5 fields have all rows overlayed by combobox controls.
The values for the list in the combobox controls comes from elsewhere
in the same spreadsheet. Please note here that the cells on top of
which the combobox controls are overlayed are NOT linked to the
combobox.

The problem is that when I try to export the data (everything including
the values in the comboboxes), since the cells themselves don't hold
the values of the comboxes, nothing gets exported for those fields. I
tried to 'Save As' 'txt' and csv' but the data from the comboboxes does
not get exported.

Another thing to note is that the names of the comboxes seems to
generated by Excel. They have names like 'Drop Down 1645', 'Drop Down
1646' etc.

Can someone help me on how to get the values of the comboboxes while
exporting or to another field (using a macro or such)? I'm a database
administrator and am not very familiar with VBA coding but can
implement it if pointed in the right direction.

Any help is greatly appreciated. I have been stuck with this for over 2
days.

Please let me know if you have any questions.

Thanks,
DB

 

[jeff]

Hi,

Why not link the cell that the combobox is hiding
to the combobox? then the selections will be there
for export.

jeff

 

[datatransfer]

Thanks for your suggestion Jeff. But here's the problem.....we'll b
getting the Excel file in this format on a daily basis. We don't wan
to manually link the cells to the combo boxes as there are about 120
such combo boxes in every spreadsheet.

Is there a way to automate this via a script? Again, please note tha
the names of the combo boxes are randomly generated - how do w
associate the cell to combo box via script?

Any help will be greatly appreciated.

Thanks,
D

 

[datatransfer]

Anything?...anybody

 

[Dave Peterson]

What kind of comboboxes are they?

Are they comboboxes from the ControlToolbox Toolbar?

Or are they DropDowns from the Forms toolbar?

The linkedcell of a combobox from the control toolbox toolbar takes the value in
the combobox. The linked cell of the dropdown (from the Forms toolbar) returns
the index into the dropdown's list.

But you could use the macro to populate a cell right under the
combobox/dropdown. But if the Dropdown changes, you'll have to refresh that
value once more. (the combobox from the control toolbox toolbar refreshes that
cell itself.)



Option Explicit
Sub testme01()

Dim myCB As OLEObject
For Each myCB In ActiveSheet.OLEObjects
If TypeOf myCB.Object Is MSForms.ComboBox Then
myCB.LinkedCell = myCB.TopLeftCell.Address(external:=True)
End If
Next myCB

End Sub

Sub testme02()

Dim myDD As DropDown
For Each myDD In ActiveSheet.DropDowns
With myDD
If .ListIndex > 0 Then
.TopLeftCell.Value = .List(.ListIndex)
End If
End With
Next myDD

End Sub