Populate ComboBox from Excel data

[Bazoz]

Hi
Is there a way of populating a ComboBox in a Word doc using VB from data in
the 3rd column of an Excel file?

I found this code, but I need it to access the data from Excel.

Private Sub ComboBox1_Change()

End Sub

Private Sub Document_Open()

With Me.ComboBox1
.AddItem "White"
.AddItem "Black"
.AddItem "Green"
End With

End Sub

 

[Helmut Weber]

Hi Bazoz,

like this:

Public Sub FillWordComboFromExcel()
' tools references
' reference to Word library set
' = early binding
' Word already running
' Word doc is open
' Word doc is not in design mode

Dim oWrd As Word.Application
Dim oDoc As Word.Document
Dim r As Long ' row

Set oWrd = GetObject(, "Word.application")
oWrd.Visible = True
Set oDoc = oWrd.ActiveDocument

For r = 1 To 3
oDoc.ComboBox1.AddItem _
ActiveWorkbook.ActiveSheet.Cells(r, 3).Value
Next

End Sub

The Excel-part could need some refinement,
like checking whether a cell's value <> ""
or adding items to the combobox only as long
as the next cell in column 3 isn't empty,
which could be done using something like:

ActiveWorkbook.ActiveSheet.Cells(65536, 3).End(xlUp).Value

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Bazoz]

Hi Helmut

Thanks for your help.

Have not been able to get your code to work. VBA is new to me.

I did find this on the web, but don’t now how to change them for my needs.
http://word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm
http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

 

[Helmut Weber]

Hi Bazoz

I need it to access the data from Excel.

You mean, you're looking for a Word macro
which reads data from Excel and adds them
to a Word combobox?

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Bazoz]

That's correct Helmut.

The client has a business application that can export data to Word via the
use of tokens (#CONTACTNAME#) in a Word template. Once the client has the
Word template open they then add additional information through a series of
ComboBoxes that I added. This works fine but the list of options can change
from time to time. So rather than have the client change the underline VBA
code it would be safe for them to change an Excel file instead. Hope that
helps.


Private Sub Tiles_Change()

End Sub

Private Sub Freize_Change()

End Sub

Private Sub Tops_Change()

End Sub

Private Sub Lacquered_Change()

End Sub

Private Sub Carpet_Change()

End Sub

Private Sub Floors_Change()

End Sub

Private Sub Document_Open()

With Me.Tiles
.AddItem "300mm x 300mm - Brown"
.AddItem "300mm x 300mm - White"
End With

With Me.Freize
.AddItem "Polished Marble Mosaic - Crema"
.AddItem "Polished Marble Mosaic - Rosso"
.AddItem "Polished Marble Mosaic - Emperador"
.AddItem "Tumbled Marble Mosaic - Crema"
.AddItem "Tumbled Marble Mosaic - Rosso"
.AddItem "Tumbled Marble Mosaic - Emperador"
.AddItem "Ceramic Mosaic - Mixed colours as per display"
End With

With Me.Tops
.AddItem "Kitchen / Bathrooms - Ice Snow"
.AddItem "Kitchen / Bathrooms - Mink"
.AddItem "Kitchen / Bathrooms - Raw Silk"
.AddItem "Kitchen / Bathrooms - Osprey"
.AddItem "Kitchen / Bathrooms - Almond Rocca"
End With

With Me.Lacquered
.AddItem "2 Pac Lacquered - Classic White"
.AddItem "2 Pac Lacquered - Parchment White"
End With

With Me.Carpet
.AddItem "Slipping Sands - Cream"
.AddItem "Slipping Sands - Stone"
.AddItem "Slipping Sands - Cork"
End With

With Me.Floors
.AddItem "Strand Woven Carbon - Semi Gloss"
.AddItem "Strand Woven Carbon - Matt"
.AddItem "Strand Woven Natural - Semi Gloss"
.AddItem "Strand Woven Natural - Matt"
.AddItem "Vertical Wide Board - Semi Gloss"
End With

End Sub