Populate Outlook Combo from Excel table

[Paul via OfficeKB.com]

Hi All,
I have created a custom task form within Outlook 2003 and all is
great and working apart from i am stuck on modification of it, i have used
comboboxes and had values added, to date they have only ever been small
values in quantity and so has not been an issue, however now i wish to have
the list content of a combobox to be from an external excel table,Idealy i
wish to have coloum A from the table as a list selection in the combobox
and when a value is selected from the combo box,have the corresponding
value in coloum B from the Excel table show in a txt box next to the combo
box on the custom outlook form.
I have a little Access knowledge but this is baffling me so any help
appriciated..

Paul

 

[Wolfram Jahn]

Paul via OfficeKB.com wrote:

the list content of a combobox to be from an external excel table

Example:

May there be a combo box and a text box on modifiedFormPage("S.2") and
an excel file at C:\myDir\myFile.xls with key/value pairs in columns A
and B, then the code below will work as specified.

This is no application, but a minimal example. Nevertheless, I added the
nothings in Item_Close, just because they are so important.

I just have just run the code in Outlook 2000.

Wolfram


------------snip----------------
dim ins
dim cts
dim wsh
dim xls

Function Item_Open()
set ins = item.getinspector
set cts = ins.modifiedformpages("S.2").controls
set xls = GetObject("C:\myDir\myFile.xls")
set wsh = xls.worksheets(1)
for each r in wsh.usedrange.rows
cts("ComboBox1").additem r.range("A1").value
next ' r
cts("ComboBox1").listindex = 0
ins.SetCurrentFormPage("S.2")
End Function

Sub ComboBox1_Click
dim i
i = cts("ComboBox1").listindex
if i < 0 then
cts("TextBox1").value = ""
else
cts("TextBox1").value = wsh.range("B1").offset(i).value
end if
End Sub

Function Item_Close()
set wsh = nothing
xls.application.quit
set xls = nothing
set cts = nothing
set ins = nothing
End Function
------------snap----------------