Populate listbox from another workbook sheet

[adamsm]

Hi anyone,

The following formula populates the list box from the active workbook's sheet "Datas"

=OFFSET(Datas!$D$17,0,0,COUNTA(Datas!$P:$P),13)

How could I change the formula so that it populates the listbox from another closed workbook.

The path for the workbook is
<code>D:\Software\Database.xlsx</code>

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Norie]

I'm not sure you can do that if the workbook is closed.

Can't you have the data for the listbox in the same workbook as it's in?

 

[adamsm]

If the data is in the same workbook the workbook does get increased in size.

What if the workbook is open? can the task be done?

 

[Norie]

Most of the time people want to keep data separate because of confidentiality, security etc not because of the size of the workbook.

How much data is going to be in the listbox?

If it's enough to affect the size of the workbook it might be worth looking at some other approach to whatever it is you are doing.

 

[adamsm]

The following code does populate the listbox from a closed workbook by opening and closing it without the user noticing.

But it does populates only a specified range. Is it possible to put the formula in the code?

Any suggestions would be kindly appreciated?

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
    With Me.ListBox1
        .Clear ' remove existing entries from the listbox
        ' turn screen updating off, 
        ' prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False 
        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\FolderName\SourceWorkbook.xls", _
            False, True)
        ListItems = SourceWB.Worksheets(1).Range("B2:B21").Value
        ' get the values you want
        SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing
        ListItems = Application.WorksheetFunction.Transpose(ListItems) 
        ' convert values to a vertical array
        For i = 1 To UBound(ListItems)
            .AddItem ListItems(i) ' populate the listbox
        Next i
        .ListIndex = -1 ' no items selected, set to 0 to select the first item
        Application.ScreenUpdating = True
    End With
End Sub

 

[Norie]

That's not really a problem, in fact it seems like all you need to do in the code you already have is change it so it use a dynamic range.

I think that's what you want to do anyway, and the formula you posted does seem to be for a dynamic range.

If you want to populate the listbox with the values from column B in the workbook you open you could try this.

Option Explicit
Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
Dim LastRow As Long
 
    With Me.ListBox1

        .Clear ' remove existing entries from the listbox

        ' turn screen updating off,
        ' prevent the user from seeing the source workbook being opened

        Application.ScreenUpdating = False

        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\SourceWorkbook.xls", _
            False, True)
          
        ' find the last row of data in column B in worksheet 1 of source workbook  
        LastRow = SourceWB.Worksheets(1).Range("B" & Rows.Count).End(xlUp).Row
        
        ' use listbox's List property to populate listbox
        Me.ListBox1.List = SourceWB.Worksheets(1).Range("B2:B" & LastRow).Value
        
        ' Close source workbook.
        SourceWB.Close
        
        Application.ScreenUpdating = True
        
    End With
    
End Sub

 

[adamsm]

Thanks for the help.


What I'm trying to get is to populate the list box from the (closed workbook's)range D9:N9 and so on as my column headers are in row 8 and data row starts from row 9.

Also how could I prevent the message that keeps on coming when I open the closed workbook "Do you want to save the changes?

Any help on this would be kindly appreciated.

 

[adamsm]

Here is my final version of the code.

Private Sub UserForm_Initialize()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
Dim LastRow As Long
 
    With Me.ListBox1

        .Clear ' remove existing entries from the list box

        ' turn screen updating off,
        ' prevent the user from seeing the source workbook being opened

        Application.ScreenUpdating = False

        ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("C:\SourceWorkbook.xlsx")
      
        ' find the last row of data in column B in worksheet 1 of source workbook
        LastRow = SourceWB.Worksheets("MySheet").Range("D" & Rows.Count).End(xlUp).Row
        
        ' use listbox's List property to populate listbox
        Me.ListBox1.List = SourceWB.Worksheets("MySheet").Range("D9:N9" & LastRow).Value
       
        
        ' Close source workbook.
        SourceWB.Close SaveChanges:=True
        
        
        Application.ScreenUpdating = True
        
    End With
 
End Sub

Now what I'm trying to get is the list box to get populated only with data rows from the range D9:N and so on.

Meaning the fisrt row of data starts from row 9. and then continues to the last data row of the worksheet between columns D & N.

Also, How to make the column headers visible in the list box?

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Norie]

Doesn't that code do what you want?

At least the first part, though I have just seen probable error.

It should be (D9:N" & LastRow) for the range of data.

As for the column headers I don't think that's going to be possible without the data being in an open workbook.

The reason for that is that if you want headers you have to use the RowSource property to populate the listbox.

For that you need to specify the range, and as far as I know you can't use an external range, which is what a range in closed workbook would be.

I'll give it a try but I honestly can't see it working even if you open then close the workbook.

Do you really need the headers in the listbox itself, they don't really add much to the it from an aesthetic point of view?

How about adding some labels above the listbox? That would give you far more options in formatting the column headers.