Import spreadsheet using Filedialog and Listbox

[De Mat]

Hello,

I'm using the Filedialog to select an Excel file which has to be imported in my Access database. After selecting the file, the path will appear in a listbox.

Now I'm using the Transferspreadsheet function to import this spreadsheet but everytime I get the error : Object Required. I hope someone can help me with this.

My code for importing the file is as follow:

'------------------------------------------------------------
' Import_BOM
'
'------------------------------------------------------------
Function Import_BOM()
On Error GoTo Import_BOM_Err
Dim db As DAO.Database
Dim rs As DAO.Recordset
    
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "BOM", List20.Value, True, ""
 Beep
    MsgBox "Ready", vbOKOnly, ""

Import_BOM_Exit:
    Exit Function
Import_BOM_Err:
    MsgBox Error$
    Resume Import_BOM_Exit
End Function

 

[Rabbie]

The error message you are getting indicates a problem with List20.value. I would advise you to put in

Msgbox(list20.value) before the transferspreadsheet line. This will let you check you are passing the correct filename to the procedure.

 

[De Mat]

The error message you are getting indicates a problem with List20.value. I would advise you to put in

Msgbox(list20.value) before the transferspreadsheet line. This will let you check you are passing the correct filename to the procedure.

edit: Hmm the listbox is the problem. It seems like I can't use the listbox's value in other forms and modules. How can I store this value for a while?

 

[Rabbie]

Have you tried Forms.frmName.list20.value should work provided the form is open. Obviouslyyou replace frmName withe actual name of the form.

 

[boblarson]

edit: Hmm the listbox is the problem. It seems like I can't use the listbox's value in other forms and modules. How can I store this value for a while?

1. As Rabbie mentioned, you can't just use the control name UNLESS it is in the same form module as the form the control is on. But, it is good to use Me.ListboxName in that instance.

2. If you reference from outside of the form you need to reference the forms collection with Forms!YourFormNameHere before the form name (notice the bang ! instead of the period). You can use the dot (.) if you reference the forms collection like Forms("yourFormNameHere").

3. I see you declaring a database and recordset object, but not using them. Is that on purpose?