Use VBA to Populate Listbox from Open Spreadsheet

[Henninger5]

My head is spinning. I have experience using VBA is a Word 2003 Doc
to retrieve a recordset from an Excel Spreadsheet and populate a
userform Listbox. However, now I want to use the VBA behind a
workbook to retrieve records from one of the sheets of the workbook to
populate a Listbox. To be specific:

- The worksheet has 20 columns of data.
- I want a recordset of the first three columns (do I refer to them by
the column letters, i.e. A,B,C, or by the column headers, i.e. first
row entry?) with the blank entries filtered out.
- I want to populate the Listbox with entries indexed the same as the
row numbers from the worksheet.

My natural inclination is to use SQL to open a recordset. However I
only know of the Jet engine that accesses a closed workbook. Can I
use it to query the workbook that's calling it? (everytime I've tried
to use Jet to query an open workbook in the past, i get funky
results). Should I even be trying to use a recordset? Is a
QueryTable better?

Please, I would appreciate someone leading me in the right direction.

Thank you.

 

[Henninger5]

Upon reflection, it seems like I should be using a Range of A:C with
the empty rows filtered out. Of course, once I have a range, how do I
loop through it to populate the listbox?

 

[Dave Peterson]

First, I don't speak the MSWord stuff too well.

But I'd approach it like this in excel's VBA:

Option Explicit
Private Sub Userform_Initialize()

Dim myRng As Excel.Range
Dim myCell As Excel.Range
Dim wkbk As Excel.Workbook
Dim Wks As Excel.Worksheet
Dim xlApp As Excel.Application

Set xlApp = New Excel.Application

Set wkbk = xlApp.Workbooks.Open(Filename:="C:\someworkbook.xls")

Set Wks = wkbk.Worksheets("Sheetnamehere")

With Wks
'assumes the data always has data in column A
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

'I'm guessing that the syntax is pretty much the same in MSWord
With Me.ListBox1
.ColumnCount = 3
.ColumnWidths = "30,30,30"
End With

For Each myCell In myRng.Cells
If myCell.Value = "" Then
'skip it, since it's empty
Else
'is this on a userform?
'I'm assuming yes and the code is in the Userform_Initialize event
If myCell.Value = "" Then
'skip it
Else
With Me.ListBox1
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = myCell.Offset(0, 2).Value
End With
End If
End If
Next myCell

wkbk.Close savechanges:=False

xlApp.Quit

Set myCell = Nothing
Set myRng = Nothing
Set Wks = Nothing
Set wkbk = Nothing
Set xlApp = Nothing

End Sub


Untested, but it did compile in excel's VBE.

 

[dksaluki]

here's another way. (this assumes your 3 column all next to each
other.) this should get you started, may have to tweak the line that
fills the array a little:

Private Sub UserForm_Click()
Dim rowCount As Integer
Dim myArray() As Variant
Dim i As Integer

'get row count and fill array
rowCount = Cells(Rows.Count, "A").End(xlUp).Row
myArray = Range("A1:C" & rowCount).Value

'fill listBox1
Me.ListBox1.ColumnCount = 3
myArray = WorksheetFunction.Transpose(myArray)
Me.ListBox1.Column = myArray

End Sub

 

[Henninger5]

First, I don't speak the MSWord stuff too well.

But I'd approach it like this in excel's VBA:

Option Explicit
Private Sub Userform_Initialize()

     Dim myRng As Excel.Range
     Dim myCell As Excel.Range
     Dim wkbk As Excel.Workbook
     Dim Wks As Excel.Worksheet
     Dim xlApp As Excel.Application

     Set xlApp = New Excel.Application

     Set wkbk = xlApp.Workbooks.Open(Filename:="C:\someworkbook..xls")

     Set Wks = wkbk.Worksheets("Sheetnamehere")

     With Wks
       'assumes the data always has data in column A
       Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
     End With

     'I'm guessing that the syntax is pretty much the same in MSWord
     With Me.ListBox1
       .ColumnCount = 3
       .ColumnWidths = "30,30,30"
     End With

     For Each myCell In myRng.Cells
       If myCell.Value = "" Then
          'skip it, since it's empty
       Else
         'is this on a userform?
         'I'm assuming yes and the code is in the Userform_Initialize event
         If myCell.Value = "" Then
            'skip it
         Else
            With Me.ListBox1
              .AddItem myCell.Value
              .List(.ListCount - 1, 1) = myCell.Offset(0,1).Value
              .List(.ListCount - 1, 2) = myCell.Offset(0,2).Value
            End With
         End If
       End If
     Next myCell

     wkbk.Close savechanges:=False

     xlApp.Quit

     Set myCell = Nothing
     Set myRng = Nothing
     Set Wks = Nothing
     Set wkbk = Nothing
     Set xlApp = Nothing

End Sub

Untested, but it did compile in excel's VBE.

Thank you!!

This'll really help!