How do I create a drop down list from another excel workbook without opening it?

With Worksheets(1)
    Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
        lb.ControlFormat.ListFillRange = Workbooks("Ports and Agents.xls").Worksheets("Ports and Agents").Range("a1:a500").Address(external:=True)

End With

doesnt work. I would really appreciate some help.

Thanks

Hi

I think you can use Office automation like this:

Sub OpenAnotherExcel()

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application

xlApp.Workbooks.Open ("Ports and Agents.xls")

Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)

lb.ControlFormat.ListFillRange = xlApp.Workbooks("Ports and Agents.xls").Worksheets("Ports and Agents").Range("a1:a500").Address(external:=True)

xlApp.ActiveWorkbook.Close False
xlApp.Quit
se xlApp = Nothing


End Sub


-vemaju