How to load data into Excel Userform Listbox from Access form directly?

Question

I want to load some data from Access into Excel Userform ListBox. What I am doing now is creating ADODB.Connection to connect access and create ADODB.Recordset to store data, firstly. Secondly, I use Range("xx").CopyFromRecordset to copy the data to excel sheet. Thirdly, name that excel range as "ResultSet". Fourthly, use Me.ListName.RowSource="ResultSet" to copy data from excel sheet to ListBox.

As you can see, I use four steps to finish this job. Is there a way to skip step 2 and step 3, copying data from Access to ListBox directly?

Thanks

Is the recordset dynamic? I'd use Excel's external data tools to import the Access data into a spreadsheet in Excel. If selections on your userform are often changing the listbox source, this might not be a performance enhancer. If the recordset is more or less static, you could set the external source and listbox source once and use Excel's refresh button any time you need to update the data source. If you are linking to a table, use the "From Access" option, otherwise "From Other Sources" > "From Microsoft Query" should give you access to either tables or queries. — MoondogsMaDawg
– MoondogsMaDawg, Commented Oct 5, 2016 at 18:45
@ChristopherD. Thanks for the reply. Yes, it is dynamic. Besides, the reason that I want to skip step 2 and step 3 is I don't want users see the data in Excel. I just want the data showing in Listbox. — kzhang12
– kzhang12, Commented Oct 6, 2016 at 16:42

kzhang12 · Accepted Answer · 2016-10-06 21:12:40Z

1

I found one article. Below is the code and this is the link.

  With rs
    .MoveLast
    NoOfRecords = .RecordCount
    .MoveFirst
  End With
  'Set the number of ListBox columns = number of fields in the recordset
  ListBox1.ColumnCount = rs.Fields.Count
  'Load the listbox with the retrieved records
  ListBox1.Column = rs.GetRows(NoOfRecords)

Thanks

answered Oct 6, 2016 at 21:12

kzhang12

2115 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MoondogsMaDawg · Accepted Answer · 2016-10-06 17:03:09Z

0

For a single column listbox

Try to manually edit the list using a loop:

Me.ListName.Clear 'First clear existing list
With Me.ListName
    While rs.EOF = False
        .AddItem rs.Fields(0).Value
        rs.MoveNext
    Wend
End With

Swap that code with steps 2-4.

answered Oct 6, 2016 at 17:03

MoondogsMaDawg

1,71413 silver badges22 bronze badges

2 Comments

kzhang12 Over a year ago

Thanks for the reply. One question: it is loading data one cell after another, right? Will it be slower than CopyFromRecordset?

MoondogsMaDawg Over a year ago

@kzhang12 Yes it loads one at a time and is slower than your current code. Depending on the size, maybe not perceptibly slower, but copying the recordset or linking to external data would be faster. You can try setting your listbox source to the recordset directly, but I've never tried that before. I use Access instead of userforms, so this is a bit outside of my experience.

Collectives™ on Stack Overflow

How to load data into Excel Userform Listbox from Access form directly?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related