We help IT Professionals succeed at work.

Browsing to a file folder and selecting a file to import, from an ACCESS FORM.

User is using EXCEL to clean up data from an absolutely horrible, irregular, unpredictable format.
After getting the data into a proper state, they then save that spreadsheet with a new name.  (they need to give it a new name in case they need to come back to it)

I'd like to build some code on an ACCESS FORM that presents the user with the ability to browse to their newly created EXCEL file, and import it into ACCESS.

I've already got the import routine built, but my VBA skills only have it far enough to import the same filename each time.

Thanks for any help.
Comment
Watch Question

President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
The easiest way to allow a user to browse for a file is using the FileDialog.  Best is to use Late Binding, so refer to

https://www.devhut.net/2016/10/04/late-binding-the-filedialog/

So in a standard module, copy/paste the following
Public Enum msoFileDialogType
'    msoFileDialogOpen = 1
'    msoFileDialogSaveAs = 2
    msoFileDialogFilePicker = 3
    msoFileDialogFolderPicker = 4
End Enum
 
 
'---------------------------------------------------------------------------------------
' Procedure : fFileDialog
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Resuable FileDialog function
' Based on  : NeoPa's FSBrowse Public Function
'             https://bytes.com/topic/access/insights/916710-select-file-folder-using-filedialog-object
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
' Req'd Refs: Uses Late Binding, so none required
'             Normally requires a Ref to 'Microsoft Office XX.X Object Library'
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' lDialogType   :
' sTitle        :
' sInitFileName :
' bMultiSelect  :
' sFilter       :
'
' Usage:
' ~~~~~~
' fFileDialog(msoFileDialogOpen,,,,"MS Excel,*.xlsx;*.xls~MS Word,*.doc;*.docx")
' fFileDialog(msoFileDialogOpen, "Which database do you wish to link to?", "C:\tmp\",,"MS Access,*.accdb;*.mdb")
' fFileDialog(msoFileDialogOpen)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-03-03              Initial Release
'---------------------------------------------------------------------------------------
Public Function fFileDialog(Optional ByRef lDialogType As msoFileDialogType = msoFileDialogFilePicker, _
                            Optional sTitle As String = "", _
                            Optional sInitFileName = "", _
                            Optional bMultiSelect As Boolean = False, _
                            Optional sFilter As String = "All Files,*.*") As String
'Ref: https://msdn.microsoft.com/EN-US/library/office/ff862446.aspx for all available properties
    On Error GoTo Error_Handler
    Dim oFd                   As Object
    Dim vItems                As Variant    'Files/Folders
    Dim vFilter               As Variant
    Const msoFileDialogViewDetails = 2    'Enum MsoFileDialogView
 
    Set oFd = Application.FileDialog(lDialogType)
    With oFd
        'Set the dialog's title
        If sTitle = "" Then
            Select Case lDialogType
'                Case msoFileDialogOpen
'                    .Title = "Browse for File to open"
'                Case msoFileDialogSaveAs
'                    .Title = "Browse for File to SaveAs"
                Case msoFileDialogFilePicker
                    .Title = "Browse for File"
                Case msoFileDialogFolderPicker
                    .Title = "Browse for Folder"
            End Select
        Else
            .Title = sTitle
        End If
        'Set the initial folder to open in
        If sInitFileName <> "" Then .InitialFileName = sInitFileName
        'Single or Multiple selection
        .AllowMultiSelect = bMultiSelect
        'Type of view to display, I'm defaulting to a detailed view
        .InitialView = msoFileDialogViewDetails
        'Set any specified filters
        If lDialogType <> msoFileDialogFolderPicker Then
            Call .Filters.Clear    'Clear any existing filters
            'as they remain in memory and are cummulative
            For Each vFilter In Split(sFilter, "~")    'Add our filters, 1 by 1
                Call .Filters.Add(Split(vFilter, ",")(0), Split(vFilter, ",")(1))
            Next vFilter
        End If
 
        If .Show = True Then
            For Each vItems In .SelectedItems
                fFileDialog = vItems
            Next
        End If
    End With
 
Error_Handler_Exit:
    On Error Resume Next
    If Not oFd Is Nothing Then Set oFd = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: fFileDialog" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Open in new window


Then using a button you simply call it by doing
Private Sub cmd_GetFile_Click()
    Dim sFile                 As String

    sFile = fFileDialog(msoFileDialogFilePicker, , , , "MS Excel,*.xls;*.xlsx;*.xlsm;*.xlsb")
    If sFile = "" Then
        'No file was chosen, user canceled file prompt
    Else
        'A file was selected, now what?
        
    End If
End Sub

Open in new window


Don't forget to adjust the filter extensions to suit your needs.
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:

Wouldn't be easier to work solely on Access...get on the data on Access..have them cleared/manipulated...etc...and only just export to Excel for viewing.