Microsoft Office Forums - View Single Post

highrise955 · 03-27-2016, 02:16 PM

Quote:

Originally Posted by gmayor

There's a thread at Store Outlook email files via VBA - Microsoft Community which demonstrates how to read an Access table into a list box, and a similar code example for Excel on my web site at Populate Userform Combo Box

Quote:

Originally Posted by macropod

See also:

…

Saving with pre-defined names & read-only password protection is managed via the SaveAs2 method.

Quote:

Originally Posted by gmaxey

Regarding #2. Often a document content control itself can serve as a pseudo external source for populating a userform listbox.

I'm ashamed to admit that I went through all three of your replies in exhaustive detail but I still don't seem to be getting it. For now I am just trying to use Excel as the data source. I tried to create a simple form with one list box control and a named range in excel with four values in the column.

Code:

Sub InitializeDocument()
 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim NoOfRecords As Long
 
    ' Open the database
    'Set db = OpenDatabase("C:\Temp\ItemSheet.xlsx", False, False, "Excel 12.0")
    Set db = OpenDatabase("C:\Temp\ItemSheet.xlsx", False, False, "Excel 12.0; IMEX=1;")
    ' Retrieve the recordset
    Set rs = db.OpenRecordset("SELECT * FROM ItemRange")
 
    ' Determine the number of retrieved records
    With rs
         .MoveLast
         NoOfRecords = .RecordCount
         .MoveFirst
    End With
 
    ' Set the number of Columns = number of Fields in recordset
    ListBox1.ColumnCount = rs.Fields.Count
 
    ' Load the ListBox with the retrieved records
    ListBox1.Column = rs.GetRows(NoOfRecords)
 
    ' Cleanup
    rs.Close
    db.Close
 
    Set rs = Nothing
    Set db = Nothing
 
End Sub

when I load the document nothing happens. No error or anything and the list box doesn't change at all. I thought maybe that "ListBox1" in the code is not right but I tried changing it to the Title of my list box and nothing happened.

Sorry for the never ending newbie questions.