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?
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.