ADO recordset to populate excel combo

[MikeAuz1979]

Hi,

Using acess and excel 2002, I have the below access vba that pulls data from local access tables and then adds the items to combo box on the excel sheet. (Connection lines omitted.)

This works brillantly a populates the excel combo with all the data, until you save and close the excel file and re-open it. Then all the data in the combo dropdown is gone.

Is there a way of making the items stay in the combo drop down even after the xl file has been closed and re-opened?

As a bit of background I was using a different method that dumped the data to an excel data sheet and the xl combo read from that but I found that the combo wouldn't update even via vba code and it would always show it's previous drop down values.

One thought I did have was to dump this data to xl like in the previous paragraph and then add vba to the excel that builds the items in the combos on the open event of the workbook. I wasn't too keen on this method as it means vba running on users PCs and this usuallys means all kinds of errors and problems.

Thanks for any help
Mike

rst.Source = "SELECT RTrim([tblNonComReport].chrFineName) FROM tblNonComReport WHERE tblNonComReport.dtmCalMondayStart = Date() - 2" & _
" GROUP BY tblNonComReport.chrFineName;"
rst.Open rst.Source, Con, adOpenKeyset, adLockOptimistic
rst.MoveLast
For i = 1 To rst.RecordCount
    rst.MoveFirst
    Do While Not rst.EOF
        acapp.Worksheets("Price Comparison").cboFD.AddItem rst.Fields(0)
        rst.MoveNext
    Loop
Next i

 

[ck1999]

I have used hidden sheets to hide combobox data and then used vba to fill combobox. These combobox were on vba forms not inside the worksheet though. This is my code

Sheets("customers").Visible = True
    Sheets("customers").Select
       For a = 1 To Range("a1").End(xlDown).Row
        cbocompany1.AddItem Range("A" & a).Value
    Next a
    Sheets("sheet1").Select
    Sheets("customers").Visible = xlVeryHidden

you could also use the listfillrange property of the combobox to customers!A1:A200 (in my case) I am not sure how to (without using vba) to select end of range. If there are extra blanks they would be at the end below the data.
This would prevent using vba.

ck1999