Use FileDialog To Select File Then Copy, Paste, Rename, Delete

Question

In Access (2016), I am trying to import various Excel files from vendors we use at my work. Their formats are all different, so I need to setup a specific saved import within Access for each one. I then want the users to be able to click the import button, and the excel file be imported to the database.

I initially used the Runsavedimportexport command, but that has a static file path and I don't fully understand transferspreadsheet to be able to use it.

My question is this, how can I use FileDialog to open the file selector, let my users select a file (probably from the desktop), then save that as a variable to run CopyFile. After CopyFile runs and pastes the copy, rename to a specific name, run runsavedimportexport then delete the file.

I have found all the various arguments for each code, but am struggling to tie it all together.

So far all I have managed to put together is

    Dim f    As Object
    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    f.Show

Exactly where is the difficulty to 'tie it all together'? Set a variable with the selected item. Use FileDialog CopyFile or Access intrinsic function FileCopy to copy file with different name. Use Kill() to delete file. When you have a procedure with specific issue (such as an error message), post question. — June7, Oct 09 '20 at 19:13
This question should be re-opened. It is not a duplicate question and was closed when it already had an answer that should have made that clear. The supposed original version of the question is 7 years old and doesn't work anymore because it now requires a reference to the Microsoft scripting library. Also this question deals with Saved Queries, Excel files, and then how to issues like file dialog and copying. — mazoula, Oct 12 '20 at 05:25

score 0 · Answer 1 · answered Oct 11 '20 at 10:49

‘assuming you have added a reference to Microsoft Scripting Library for file system manipulation

‘assuming you have added a reference to Microsoft Office Object 16 library for file dialogs

‘assuming you have used the external data-tab to save some imports from a hidden file named c:\yourdirectory\importme.xlsx

‘assuming you also stored the names of those imports in an access tabled called QueryNames

Then create a form with a listbox and a button. Set the rowsource of the listbox to the QueryNames Table and set the listbox.multiselect to none.

Here is the code:

Private Sub cmdImportData_Click()
Dim fso As FileSystemObject
Dim filedialog As Office.filedialog
Dim filepath As String
dim targetpath as string: targetpath = c:\yourdirectory\importme.xlsx
Set fso = New FileSystemObject
Set filedialog = Application.filedialog(msoFileDialogFilePicker)
filedialog.AllowMultiSelect = False
If filedialog.Show = True Then
    filepath = filedialog.SelectedItems(1)
    fso.CopyFile filepath, targetpath, True
    Dim FirstQueryID As Integer: FirstQueryID = 1 ''crude way to select between 2 stored queries
    Dim SecondQueryID As Integer: SecondQueryID = 2
    If listboxofQueryNames = FirstQueryID Then
       DoCmd.RunSavedImportExport ("MyFirstSavedQuery")
       fso.DeleteFile targetpath
    End If
Else
    DoCmd.RunSavedImportExport ("MySecondSavedQuery")
    fso.DeleteFile targetpath
End If

End Sub

Helpful Links https://learn.microsoft.com/en-us/office/vba/api/access.application.filedialog Copy and paste a file through access vba

Use FileDialog To Select File Then Copy, Paste, Rename, Delete

1 Answers1