PDA

View Full Version : Add contents of cells to combo box



Tezzies
12-11-2007, 08:11 AM
I have a word doc with a combo box.
I am trying to populate the box with the contents of column A.
The amount of rows used varies.

I triesdto just add the data from A1 to start with but I could'nt even manage that.

here is my try
the following code is held held on my word userform


Workbook.open ("C:\test.xls.)
cbxsup.additem Workbooks("C:\test.xls.).Sheets("Sheet1").Range("A,1")

carlmack
12-11-2007, 10:13 AM
You could try something like :-


Public Sub ImportFromExcelLateBinding()
Dim xl As Object
Dim wb As Object
Dim ws As Object
Dim LastRow As Long
Dim Counter As Long
Set xl = CreateObject("Excel.Application")
Set wb = xl.workbooks.Open("C:\Test.xls")
Set ws = wb.worksheets("Sheet1")
LastRow = ws.Range("A" & ws.Rows.Count).End(3).Row
Me.cmbTest.Clear
For Counter = 1 To LastRow
Me.cmbTest.AddItem CStr(ws.Range("A" & Counter).Value), Counter - 1
Next Counter
wb.Saved = True
wb.Close
xl.Quit
Set ws = Nothing
Set wb = Nothing
Set xl = Nothing
End Sub



Assuming this code is in the document module, your combo is called cmbTest and the list is in Sheet1 Range A1 downwards of c:\test.xls.

HTH
Carl

Bob Phillips
12-11-2007, 10:17 AM
CarlMack?

I only know one person in Cyprus. Don't tell me that OzGrid has banned you as well?

carlmack
12-11-2007, 10:25 AM
LOL . No Oz still puts up with me but I just saw a link here and thought I'd take a look around. I see you have been here for quite a while !