Avatar of Rachel
Rachel
Flag for United States of America asked on

Need code to import file from excel into Access DB

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)

Client2
    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"
Else
MsgBox "Users have not been imported"

End If

End Sub
Microsoft Access

Avatar of undefined
Last Comment
als315

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rachel

ASKER
Thanks Pat - I don't actually know how to write that though.  Can we schedule time so you can show me how to do this?
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rachel

ASKER
Thanks again Pat, but I am really new to VBA and don't understand where to put that code in my existing code in order to make it work.  I need someone to walk me through it (literally.)
SOLUTION
als315

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy