Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008

    Unanswered: Simple file navigation macro, need help creating one.

    I have a database that will be e-mailed to various users so having a macro to import a file into a specific table doesn't seem to work because I must define the file path in the VBA code. Even with a prompt you still have to at least put in part of the path name, which will be different for each user. Does anyone happen to know of a simple macro that simply starts the import prompt?

    One that removes the need to go to File --> Get External Data --> Import, manually and simply brings the first prompt up for you where you choose the file.

    If access had a macro recorder I would simply record those three steps and stop it from there.


  2. #2
    Join Date
    May 2008
    Raleigh, NC

    A couple of ways...

    There are a couple of ways, but I don't think any are really 'user-proof'.

    Where is the file going to be located on the user's machine? Have you made it a standard location? If it is standard the path could be hardcoded into the macro. But you would to code for the exception of the file not being there.

    If you have it put in the same place as the mdb file, you can use code to get the 'application' path and open a dialog box with that path.

    Hope that helps.

    --If its free, take it for what its worth!

  3. #3
    Join Date
    Aug 2008
    That is essentially the problem. There is no standard location for the files. I'd rather not over complicated the macro by including code exception. It would work well with just the prompt that I described in my first post to simply automate the start of the import. Any ideas on how that can be done in a macro?

  4. #4
    Join Date
    May 2008
    Raleigh, NC

    Way too much time on my hands.....

    I created a button on a form (btnFileLocation) and assigned the following code to the on_click event. I think if you have a macro that calls this code and uses the returning value you can probably make it work.

    Good luck, Stu

    Private Sub btnFileLocation_Click()
    On Error GoTo EndNow

    ''Any code with the double tick is mine, a single tick came from the help

    ''I had to add the following references for this to work
    ''Microsoft Office 12.0 Object Library
    ''Microsoft Office 1.0 Type Library

    ''I stole/modified most of this code from some help files but it will bring back
    ''the entire path and file name of the file that the user selects from the dialog

    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    Dim strFile As String ''contains path and filename

    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd
    'Allow the user to select multiple files.
    ''I changed this to allow only one selection
    .AllowMultiSelect = False
    'Use the Show method to display the File Picker dialog box and return the user's action.
    'If the user presses the action button...
    If .Show = -1 Then
    'Step through each string in the FileDialogSelectedItems collection.
    For Each vrtSelectedItem In .SelectedItems
    'vrtSelectedItem is a String that contains the path of each selected item.
    'You can use any file I/O functions that you want to work with this path.
    'This example simply displays the path in a message box.
    MsgBox "Selected item's path: " & vrtSelectedItem
    strFile = vrtSelectedItem
    MsgBox "You can have your macro grab the string " & vbLf & _
    strFile & " to pass the filename and path", vbOKOnly, "Good Luck!"
    'If the user presses Cancel...
    ''You could a message box or something here
    End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing

    Exit Sub

    Exit Sub

    End Sub
    --If its free, take it for what its worth!

Posting Permissions

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