Microsoft Office Forums - View Single Post - Data from Excel to Listbox multiselect to Word

marksm33 · 02-15-2014, 01:40 PM

Hello, fellow members,

I'm a bit stuck. I'm pulling data for a multiselect listbox in Word from a range in Excel. The range is referred to as "Rub1a" The Excel file's name is "TE1.xltm". The listbox's name is "lb1a". The userform's name is UF1a.

When I run the code below (big thanks to Greg Maxey for helping me get this far), I get the "Run-time Error 1004 App-defined or object Defined Error."

The range only has one column in it and it is static.
When I debug the code, the error pops up on the columncount line, which is set = 1.

I've tried to use the same code with a different non-macro Excel file, to see if that was the problem, but the same error occurs.

I've checked the spellings of all the controls, files and ranges and they are accurate.

I'm using a late binding method.

Any ideas about where I went wrong? I feel like it's staring me right in the face and I'm just missing it

Could it be with something from my Excel settings? I'm using Word and Excel 2010 on a Windows 7 machine.

Thanks, Mark

Code:

Private Sub Userform_Initialize()
     'Late binding. No reference to Excel Object required.
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim cRows As Long
    Dim i As Long
    Set xlApp = CreateObject("Excel.Application")
     'Open the spreadsheet to get data
    Set xlWB = xlApp.Workbooks.Open("C:\Users\mark.miller\Desktop \TE1.xls")
    Set xlWS = xlWB.Worksheets(2)
    cRows = xlWS.Range("Rub1a").Rows.Count - xlWS.Range("Rub1a").Row + 1
    LB1a.ColumnCount = 1
     'Populate the listbox.
    With Me.LB1a
        For i = 2 To cRows
             'Use .AddItem property to add a New row For Each record And populate column 0
            .AddItem xlWS.Range("Rub1a").Cells(i, 1)
             'Use .List method to populate the remaining Columns
            .List(.ListCount - 1, 1) = xlWS.Range("Rub1a").Cells(i, 2)
            .List(.ListCount - 1, 2) = xlWS.Range("Rub1a").Cells(i, 3)
        Next i
    End With
     'Clean up
    Set xlWS = Nothing
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
lbl_Exit:
    Exit Sub
    End Sub
     
Private Sub cb1a_Click()
    For i = 0 To lb1.ListCount - 1
        If lb1.Selected(i) = True Then
            strDocs = strDocs & lb1.List(i) & vbCr
        End If
    Next i
    strDocs = Left$(strDocs, Len(strDocs) - 1)
    ActiveDocument.Bookmarks("bm1a").Range _
    .InsertBefore strDocs
    UserForm1.Hide
End Sub