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.

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?

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.xl s")

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.







On 10/06/2010 12:50, Henninger5 wrote:
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

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

On Oct 6, 5:53*pm, Dave Peterson wrote:
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..x ls")

* * *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.

On 10/06/2010 12:50, Henninger5 wrote:

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

Thank you!!

This'll really help!