[RESOLVED] rowsource in different file

[Resource Dragon]

I am working on a user form attached to a Word template. I want to populate a combo box in this form and use an excel spreadsheet for the combo box’s rowsource. I’ve got Word and Excel nodding to each other by referencing the Excel Object library and then
Dim mySpreadsheet as Excel.Workbook
Set mySpreadsheet = GetObject(“G:\...\all 10 11.xls”)

Yes, I’m working with Word 2003 & Excel 2003.
It’s the next line that’s been giving me problems. I’ve tried naming the range in the spreadsheet and then referring to the named range. I’ve tried referring to cells A2:A300. Either way I keep on getting error number 9 (subscript out of range), or 424 (object required). (Assume the combobox is called cmbID, the named range is called ID_Nos, and the sheet it is on is called 10 11 summary.)
Can someone please help me with the syntax?
Thank you

[Siddharth Rout]

Does this help? In this method you don't need to Early Bind (i.e reference the Excel Object Library) so that it works across Excel 2003/2007/2010

I have used Late Binding.

Private Sub UserForm_Initialize()
    Dim oXLApp As Object, oXLWB As Object, oXLSheet As Object
    Dim MyArray As Variant
    
    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")
    
    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
        blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0

    oXLApp.Visible = False
    
    '~~> Change the pathname and file as applicable
    Set oXLWB = oXLApp.Workbooks.Open(FileName:="C:\Sample.xls")
    
    '~~> Work with the relevant Sheet
    Set oXLSheet = oXLWB.Sheets("10 11 summary") '<~~ Your Sheet

    MyArray = oXLSheet.Range("ID_Nos")
    For i = 1 To UBound(MyArray)
        cmbID.AddItem MyArray(i, 1)
    Next i

    '~~> Clean Up
    oXLWB.Close
    oXLApp.Quit
    
    Set oXLSheet = Nothing
    Set oXLWB = Nothing
    Set oXLApp = Nothing
End Sub

[Resource Dragon]

Dear Koolsid

Thank you very much for that. It looks like it should do the trick. I'll let you know how I get on tomorrow (it's quite late in Oz).

[Siddharth Rout]

I have tested it and it works

Let me know if you are face any problems with it

[Resource Dragon]

Dear Koolsid

Thanks for that. SOLID GOLD CODE! Worked straight away.