Visual Basic 6 search excel 2007 drop down list
Posted on 2013-01-20
I have a Visual Basic 6 program that opens up an existing Excel spreadsheet using OLE automation.
The spreadsheet has a number of drop down lists that are pre populated with values.
I am looking for a way to find two matching values in the drop down list (two parts of the same exel drop down list item) and select them using OLE commands from Visual basic.
For example: I have two search terms - make of car and year of production, for example:
'Holden' and '1973'. The make of car and year of production will change depending upon user selection within the VB6 application.
Excel has prepopulated drop down lists.
Unfortunately, when the lists were created, they were of a non standard format (the lists cannot be altered to a standard format as they are centrally set by the organisation). The format for the items in the list can range from :
Holden - 1973
Ford - 1960
Ferrari - 1959
As stated I need the ole automation commands for Visual basic that will scan for 'Holden' and '1973' in any of the excel drop down list items (ignoring the number of spaces and hyphen between) and select this item from the drop down list programattically.
There are a number of these (non standardly formatted lists) that must be searched in the same excel spreadsheet.
Many thanks for any assistance!