troubleshooting Question

Need code to import file from excel into Access DB

Avatar of Rachel
RachelFlag for United States of America asked on
Microsoft Access
5 Comments3 Solutions95 ViewsLast Modified:
I have the code to import a static file, but what I'd like to do is create code that will look to the folder that the DB resides in and import the excel file in that folder.  I will be copying this DB multiple times for different clients and the import should look to the folder for whatever client the DB is in (I will use the same excel file name each time.)

Here's the folder structure:

Client 1 (folder name)
    AccessDB_Client1 (access db name - could be any name)
    Source Files (folder name)
        tblPolicy.xls (file name)

    AccessDB_Client2 ( (access db name - could be any name)
    Source Files (folder name)
        tblPolicy.xls  (file name)

So if the Access DB is client 1 or client 2 (or any client folder for that matter), the import file will look to the appropriate folder and import the tblPolicy.xls that applies to that client in the Access DB that is also in that client's folder.

Here's the code I'm already using.

Happy to pay for live help as well!

Private Sub cmdImportUsers_Click()

Dim Response As Integer
Response = MsgBox("You are about to import user data which will overwrite any existing manually changes you've made in this system.  Are you sure you want to continue?", vbYesNo)
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "z_qry_Delete_z_tbl_TAM_Users"
DoCmd.RunSavedImportExport "Import-z_tblSourceEmployees"
DoCmd.SetWarnings True
MsgBox "Users Imported Successfully"
MsgBox "Users have not been imported"

End If

End Sub
Join our community to see this answer!
Unlock 3 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros