VBA Prompt User to Select Excel File to Import

[BigNate]

Hello Everyone,

I'm struggling with this one. I have a button called "BtnImportExcel". I want to click this button and have a box appear so that the user can select the Excel file the they want to import, much like an email attachment.

Can someone please tell me how to do this or point me in the right direction? Thanks!

 

[ranman256]

Add this code to the button click. NOTE, BE SURE TO ADD : Microsoft Office 11.0 Object Library
to the project REFERENCES.

usage:

sub btnImportExcel_click()
vFile = UserPick1File("c:\folder\")
if vFile <> "" then
    docmd.transferspreadsheet .....
endif
end sub






Public Function UserPickFile(byval pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String
dim fd as filedialog


set fd = Application.FileDialog(msoFileDialogFilePicker)  '<---- 'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
With fd
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "All Files", "*.*"
    .Filters.Add "Excel Files", "*.xls*"


    .InitialFileName = pvPath
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    ''.AllowMultiSelect = True
     
        If .show = 0 Then
           'There is a problem
           Exit Function
        End If
    
    'Save the first file selected
    UserPickFile = Trim(.SelectedItems(1))
End With
set fd = nothing
End Function

 

[BigNate]

Hey thanks a lot. One more question for you if you don't mind--what do I do with the line docmd.transferspreadsheet? How do I know what to put here, if it is the file that changes from one click to the next?

 

[ranman256]

that is the import command.
to import the file chosen into the table...file out the items to fit your db.

 

[BigNate]

Got it working now. Thanks!