How to import a spreadsheet through a form

No, I did not say the Microsoft ACCESS xx.x Object Library I said the Microsoft OFFICE xx.x Object Library. These are different libraries. Scroll down to find the correct library (they are in alphabetical order. All you need to do is check off the Office Object Library (12.0 for you).

Forgive my ignorance here but I am using Access 2007.  When I am in the visual basic code and select Tools and then References I see a reference for Microsoft Access 12.0 Object Library selected.  This object library is stored at the C:\Program Files(x86)\Microsoft Office\Office 12\MSACC.OLB.  Do I need to reference this object library and location in the coding even if the reference to the object library is selected in the tools - references menu?  If so how would I do this and I am just wondering if perhaps you could explain to me a little bit about what is going and why a specific reference to the object library might be required when to a casual uninformed user like me, it appears I already have the library selected? 

Thanks,

Terry

These days I'd use the FileDialog object, but for many years I used Bill Wilson's freely distributed BrowseForFileClass class module.  You'll find this still used in DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates how to import and decompose an Excel worksheet into a set of correctly normalized tables, but for a basic import the code in the import button's Click event procedure can be pruned to something like this:

Private Sub cmdImport_Click()

    On Error GoTo Err_Handler

    Dim OpenDlg As New BrowseForFileClass

    Dim strPath As String

    OpenDlg.DialogTitle = "Select File"

    OpenDlg.DefaultType = "*.xlsx"

    strPath = OpenDlg.GetFileSpec

    Set OpenDlg = Nothing

    ' exit procedure if user cancelled

    If strPath = "" Then Exit Sub

    ' import Excel data into a table named NewTable

    DoCmd.TransferSpreadsheet _

        TransferType:=acImport, _

        TableName:="NewTable", _

        FileName:=strPath, _

        HasFieldNames:=True

Exit_here:

    Exit Sub

Err_Handler:

    MsgBox Err.Description, vbExclamation, "Error"

    Resume Exit_here

End Sub

You need to add a reference to the Microsoft Office Object Library for your version. The FileDialog object is in there. From there you can use it to use the standard Windows File Open dialog to select a file and store the path in a variable that can be used n the TransferSpreadsheet method. 

Following is code I use for this task:

Private Sub cmdBrowseForFile_Click()

On Error GoTo Err_cmdBrowseForFile_Click

Dim fDialog As Office.FileDialog

Dim varfile As Variant

Dim strPath As String

Dim strFilter As String, strFilename As String

   ' Requires reference to Microsoft Office x.xx Object Library.

strPath = CurrDBDir()

   ' Clear listbox contents.

'   Me.FileList.RowSource = ""

   ' Set up the File Dialog.

   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

   With fDialog

      ' Allow user to make multiple selections in dialog box

'      .AllowMultiSelect = True

      ' Set the title of the dialog box.

      .Title = "Please select one or more files"

      .InitialFileName = strPath

      ' Clear out the current filters, and add our own.

      .Filters.Clear

      .Filters.Add "Excel Files", "*.XLS*"

      .Filters.Add "Text Files", "*.TXT"

      .Filters.Add "CSV Files", "*.CSV"

'      .Filters.Add "Excel Files", "*.XLSX"

'      .Filters.Add "Access Databases", "*.MDB"

'      .Filters.Add "Access Projects", "*.ADP"

'      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the

      ' user picked at least one file. If the .Show method returns

      ' False, the user clicked Cancel.

      If .Show = True Then

         'Loop through each file selected and add it to our list box.

         For Each varfile In .SelectedItems

            Me.txtFilename = varfile   'Assigns selected path and file to control on a form.

         Next

      Else

         MsgBox "You clicked Cancel in the file dialog box."

      End If

   End With

'

''Sets filter for acceptable file types

'    strFilter = ahtAddFilterItem(strFilter, "Import Files (*.xls,*.dbf,*.csv,*.mdb)", "*.XLS;*.DBF;*.CSV;*.MDB")

'

''Calls module to load File open dialog box for A: drive and selected File types

'strInputFileName = ahtCommonFileOpenSave(InitialDir:="H:\Accutrac\Accutrac Access\Imports", _

'        Filter:=strFilter, OpenFile:=True, Flags:=ahtOFN_HIDEREADONLY, _

'        DialogTitle:="Select File for Input...")

'

''Checks for valid selection and stores path

'If strInputFileName <> "Cancel" Then

'    Me.txtFilename = strInputFileName

'    If Right(strInputFileName, 3) = "mdb" Then

'        Me!strTable.Visible = True

'    Else

'        Me!strTable.Visible = False

'    End If

'End If

Exit_cmdBrowseForFile_Click:

    Exit Sub

Err_cmdBrowseForFile_Click:

    MsgBox Err.Description

    Resume Exit_cmdBrowseForFile_Click

End Sub

Note: this code writes the path and file to a control on the form (Me.txtFilename), You can assign it to a variable instead and add the TransferSpreadsheet method.

The Access Web has an API that calls the Windows Common Dialog that can be used with Access VBA.

I've been using starting with Access 97, and it still works with Access 2013.

http://access.mvps.org/access/api/api0001.htm

I use this to allow the user to browse for a location for the "back-end" file for the purposes of re-linking should it become necessary.