Open File using VBA (1 Viewer)

[smh101]

Hello There!

Was wondering if anyone can help out a novice on access.

I am trying to create a basic code where I can browse my local network to locate a file and then import it into a table I have already created.

I would also like to be able to export a number of tables in my database automatically using VB code.

Any help would be very much appreciated!

 

[Moniker]

To browse a directory, you need a FileDialog object. In the VBA code window, go to References and add the MS Office Object Library. It will be named like this:

Microsoft Office [Version_Number] Object Library

If you don't have a reference to the Office Object Library, you will not be able to DIM an object as a FileDialog.

From there you need to instantiate the dialog, like this:

Sub BrowsingWindow()

    Dim Dlg As FileDialog
    Dim txtFilePath As String
    
    Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
    With Dlg
        .Title = "Select the file you want to import"
        .AllowMultiSelect = False
        If .Show = -1 Then
            txtFilePath = .InitialFileName
        Else
            Exit Sub
        End If
    End With

End Sub

The FileDialog object has a lot of properties that you can look up in Access help. For a quick reference, the "Show = -1" means the user clicked OK in the dialog, and the ".InitialFilename" is the filename that was selected.

 

[The_Doc_Man]

Please don't multi-post the same question in two different places. As you are a novice, we can forgive you - but it is bad manners in this particular forum because it represents a waste of time.

 

[[]v[]|2 2]

To browse a directory, you need a FileDialog object. In the VBA code window, go to References and add the MS Office Object Library. It will be named like this:

Microsoft Office [Version_Number] Object Library

If you don't have a reference to the Office Object Library, you will not be able to DIM an object as a FileDialog.

From there you need to instantiate the dialog, like this:

Sub BrowsingWindow()

    Dim Dlg As FileDialog
    Dim txtFilePath As String
    
    Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
    With Dlg
        .Title = "Select the file you want to import"
        .AllowMultiSelect = False
        If .Show = -1 Then
            txtFilePath = .InitialFileName
        Else
            Exit Sub
        End If
    End With

End Sub

The FileDialog object has a lot of properties that you can look up in Access help. For a quick reference, the "Show = -1" means the user clicked OK in the dialog, and the ".InitialFilename" is the filename that was selected.

Hi i tried the code from above but when the function is executed an error occurs: Method or Data member not found

It's referring to the Application.FileDialog(..) command, Application. works fine, but it can't find the FileDialog method.

Help?

 

[pungentSapling]

Same problem

I too am having the same problem. I have the reference library check (office 10) and have tried several bits of code but alway come back with the same problem. Any comments?

 

[freakazeud]

re:

Hi,
I would avoid the FileDialog and implement an API solution instead.
HTH
Good luck

 

[pungentSapling]

File look up

I tried your suggested code and get a similar response to the previous method. I think it might have to do with the reference library, but in both cases, I have selected the required reference library and still does not work?