Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2016
    Posts
    5

    Answered: Allow a User to browse their files For importing.

    I am working in the latest version of access and I want to create a button on a form which will import an excel file to an existing table.

    I have created the form and the button but don't know how to prompt the user and allow them to browse files (the way they would when selecting a location to save a file).

    It is very easy to hard code an address but the address will never be constant. I want the user to be able to select an XML file of their choice.
    Formatting wont be a huge issue from the XML file because The tables will all have the same fields there are just many to choose from.

    Thanks in Advance,

    o3smog

  2. Best Answer
    Posted by ranman256

    "usage:
    txtBox = UserPick1File("c:\folder\")


    Code:
    Public Function UserPick1File(Optional pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    '===================
    'YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
    '===================
    
    With Application.FileDialog(msoFileDialogFilePicker)   
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls;*.xlsx"
        .Filters.Add "All Files", "*.*"
        .InitialFileName = pvPath
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        UserPick1File = Trim(.SelectedItems(1))
    End With
    End Function
    "


  3. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Check the help file for information on Application FileDialog.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    470
    Provided Answers: 22
    usage:
    txtBox = UserPick1File("c:\folder\")


    Code:
    Public Function UserPick1File(Optional pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    '===================
    'YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
    '===================
    
    With Application.FileDialog(msoFileDialogFilePicker)   
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls;*.xlsx"
        .Filters.Add "All Files", "*.*"
        .InitialFileName = pvPath
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        UserPick1File = Trim(.SelectedItems(1))
    End With
    End Function

  5. #4
    Join Date
    Jul 2016
    Posts
    5

    Thanks

    Thank you this was helpful.

    Unfortunately in access 2016 the Microsoft Office 11.0 Object Library isn't available (at least not that I could find on my machine).

    But I fiddled with it for a while and this is what I ended up with. It is not advanced but gets the job done.

    Code:
    Private Sub BtnImportTumors_Click()
    'make a variable'
    Dim FD As Object
    
    'use file picker which is available in access 2016'
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    With FD.allowMultiSelect = False
    
     If FD.Show = -1 Then
     
    
    End If
    End With
    'Import whatever file is selected by user'
     DoCmd.TransferSpreadsheet acImport, , "Tumor_Import", FD.SelectedItems(1), True
     'clean up variable'
    Set FD = Nothing
    End Sub
    I appreciate your responses and if this code could be improved in some way I am always open to suggestions.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •