View Full Version : Solved: Get Excel data for Outlook UserForm

01-02-2008, 12:28 PM
I have an Outlook UserForm that needs to populate a ComboBox from an Excel spreadsheet.

However, i am new at all this and cannot work out how to open a specified workbook / spreadsheet / cell and obtain the values to populate the Combobox.

Thanks all

(Windows XP / Office 2003)

01-02-2008, 02:28 PM
It's actually not to hard. You will want to go to the tools menu of the VBE and set a reference to Microsoft Excel then you can pull that data as needed using the 4 basic excel objects (Application, Workbook, Worksheet, Range).

Here is some example syntax that should get you started. Make sure you set the reference before you try to use the code.

Sub Example()
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rng As Excel.Range
Set xlApp = New Excel.apl
Set wb = xlApp.Workbooks.Open("C:\Test.xls", Radonly:=True)
Set ws = wb.Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
MsgBox rng.Value
wb.Close SaveChanges:=False
End Sub

01-02-2008, 02:48 PM
Thank you very much
That has worked well.