Transfer excel data to access table by choosing file from windows explorer.

I’m trying to find vba to transfer an Excel file of my choosing from Windows Explorer into an existing table in Access without opening the Excel file.

I would like this to be attached to a button's on click event in Access.

I've only found code to open a folder but the file is opened when clicked and also one that only shows the folders and not the files within the folder.  Any help would be appreciated.
Annmv888Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Do you have code to import from the Excel file to Access?

If you have that it would be easy to add functionality to it that allows you to select the file to import.
0
Annmv888Author Commented:
I do but it transfers a range from the current days Excel document.  They have to be able to make their own choice.

Here it is:

Dim strXls As String
strXls = CurrentProject.Path & Chr(92) & "Sent to AP\ChecksReceived" & Format(Date, "mmddyyyy") & ".xls"

DoCmd.TransferSpreadsheet acImport, , "tblChecksReceived", _
    strXls, True, "APChecks"
0
NorieAnalyst Assistant Commented:
You could try using this function to allow the user to pick a file.
Function XLSFilePicker(Optional strPath As String) As String
Dim dlg As Object
Dim varFile As Variant
   
    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
   
    If Len(strPath) > 0 Then
        ChDir strPath
    End If
   
    With dlg

        .AllowMultiSelect = False
        .Title = "Please chose the file to import"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls*"
        .Filters.Add "All Files", "*.*"

        If .Show = True Then
            XLSFilePicker = .SelectedItems(1)       
        End If
   End With
   
End Function

Open in new window


It would look something like this in your posted code.
Dim strXls As String

    strXls =XLSFilePicker(CurrentProject.Path & Chr(92))

    If strXls = "" Then
       MsgBox "Operation cancelled"
       Exit Sub
   End If
    DoCmd.TransferSpreadsheet acImport, , "tblChecksReceived", _
        strXls, True, "APChecks"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
If you want them to specify a range, you will need to link the worksheet rather than importing it.
DoCmd.TransferSpreadsheet acLink, , "tblChecksLinked", _
    strXls, True, "APChecks"

On your form, create two unbound textboxes.  One with a start date and the other with an end date.  If most of the time you only want to import a single day, have the afterUpdate event of the first textbox put the date into the end text box

Me.txtEndDT = Me.txtStartDT

Now create a query that selects rows for this date range and appends them to tblChecksReceived.

To set this up the first time, you will need to link the spreadsheet manually so that the table named tblChecksLinked exists which will allow you to create the append query.  After the first time, the code will handle the linking.
0
Annmv888Author Commented:
This worked exactly how I wanted it to...thank you so much for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.