Results 1 to 5 of 5
  1. #1
    Betteron2wheels's Avatar
    Betteron2wheels is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2019
    Location
    Maryland
    Posts
    3

    Use file dialog to allow user to browse for file

    Hi All,

    I've been beating my head against a wall trying to figure this out. I have a DB set up and running that needs to import an excel spreadsheet to update various table. I have a working solution using a save import, but I don't want each new user/computer to need to set up the saved import and be restricted to exact file name and folder.



    I don't have much experience with VBA
    .

    I would like to set it up so the user will press a button that will prompt them to browse to their file location so they can x2 click to import.

    I tried a few variations of file dialog use from the web that worked in videos but when I try them on my machine I get this error

    "Compile error
    User-defined type not defined"

    When I try to run it from the VBA editor with F5 I get a message box to enter a macro name.

    I have Private Sub ImportVendorTemplate_Click()
    set to match my button


    I have "Microsoft Office xx Object Library" checked but no luck.

    The VBA below is not for .xlsx but I tried to use it to see if I could get anything to work.

    Below is the VBA:

    Private SubImportVendorTemplate_Click()

    Dim fd As FileDialog

    Set fd =Application.FileDialog(msoFileDialogFileopen)
    With fd
    .AllowMultiSelect = False

    .Filters.Clear
    .Filters.Add "Any File","*.*", 1
    .Filters.Add "Comma SeparatedFile", "*.csv; *.txt", 2
    .Filters.Index = 2

    If .Show = True Then
    Me.txtFileName.Value =.SelectedItems.Item(1)

    End If

    End With

    End Sub


    Thanks in advance!


  2. #2
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    125
    Can you specify exactly which item is highlighted when you get that error?

  3. #3
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,602
    Please use code tags when posting code......

    You were close - here is you file picker code (the corrections are in blue)
    Code:
    Private Sub ImportVendorTemplate_Click()
        Dim fd     As FileDialog
    
        'Set fd = Application.FileDialog(msoFileDialogFileopen)  '<<-- this in not correct
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
        With fd
            .AllowMultiSelect = False
            .Filters.Clear
            .Filters.Add "Any File", "*.*", 1
            .Filters.Add "Comma SeparatedFile", "*.csv; *.txt", 2
            .FilterIndex= 2
    
            If .Show = True Then
                Me.txtFileName.Value = .SelectedItems.Item(1)
                '            Debug.Print fd.SelectedItems(1)
            End If
        End With
    
    End Sub

    The MsoFileDialogTypes are
    msoFileDialogOpen Open dialog box
    msoFileDialogSaveAs Save As dialog box
    msoFileDialogFilePicker
    File picker dialog box
    msoFileDialogFolderPicker
    Folder picker dialog box




    You might want to look at https://analystcave.com/vba-applicat...g-select-file/
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    JeffGrant is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Oct 2021
    Posts
    9
    Hi All,

    I have seen this code and similar code around. However, where I am getting stuck is the Me.txtFileName.Value property.

    I am using Access 365 and ths txtFileName property is not an option anywhere.

    Does this property go by a different name in Access 365?

    thanks

    Jeff

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,028
    That is meant to be the control on your form.
    Me is shorthand for the form name, txtFileName is the name of the control.

    When you see code like this. it is just an example, you need to modify to suit your needs.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 02-22-2014, 02:51 PM
  2. Replies: 13
    Last Post: 12-12-2013, 07:22 PM
  3. Button to Browse for File!
    By floyd in forum Forms
    Replies: 5
    Last Post: 08-23-2013, 09:09 AM
  4. Browse to external file while in a form
    By michaeljohnh in forum Import/Export Data
    Replies: 9
    Last Post: 09-22-2010, 09:33 AM
  5. Browse for file
    By ccpine@comcast.net in forum Database Design
    Replies: 0
    Last Post: 08-24-2008, 10:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums