Import Excel Using VBA

[Sys]

Can anyone point me in the right direction on how to import an Excel spreadsheet into a form listbox using VBA?
Thanks.

[ranman256]

You don't need vba, but the list box is filled from a query/table, not an import.

in a macro
transfersreadsheet

or vba: docmd.transferspreadsheet

[Sys]

Thanks for the reply.
Yeah I was thinking about it the wrong way. I was able to import the spreadsheet to a table using the docmd.transferspreadsheet upon button click within a form. Now I am trying to get another form's listbox to populate from the table using Me.Listbox1.RowSource = "SELECT * FROM [Inventory]". Still think I am missing something though.

[Sys]

This is what I am experimenting with as of now:

Private Sub Form_Initialize()


Dim strTable As String
strTable = Me.Listbox1
Me.Listbox1.RowSourceType = "Table/Query"
Me.Listbox1.RowSource = "SELECT * FROM Inventory"
Me.Listbox1.Requery

End Sub

[ranman256]

No sys, you dont need any vb code. (remove your form_init code)
The controls work from the form with zero code.
If you import from the Excel file, then open the form, the control will populate itself, as long as you have the sql in the control property like you mentioned.

If you import data while the form is open, then it list/combo must be refreshed using Me.Listbox1.Requery,
or close the form, and reopen.

[Sys]

Unfortunately I have to use VBA because it is a school project that requires it. We had to create a form with a button that will delete records in a table and repopulate it with a spreadsheet (Which I have accomplished). And another form that populates a listbox with the data from that same table which I am trying to figure out. I'm not getting any errors but my listbox is blank.

[ItsMe]

... I'm not getting any errors but my listbox is blank.

The ListBox gets its data from the table. After the import process (next line of code) .Requery the Listbox. See if that works.