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
RachelAsked:
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.

PatHartmanCommented:
The problem is that you cannot use DoCmd.RunSavedImportExport because it has no flexibility.  You have to use DoCmd.TransferSpreadsheet.  That will allow you to change the source on the fly.

currentdb.Name

will give you the full path to the FE database.  You can pull out the path part and concatenate with your excel file name for use in the TransferSpreadsheet method.
1

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
RachelAuthor Commented:
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?
0
PatHartmanCommented:
I don't do GIGs if that is what you are asking.  Here's an example from one of my apps.

            FileName = Me.txtPath & Me.cboQueries & "_" & Format(Date, "yyyymmdd") & ".xlsx"
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, Me.cboQueries, FileName, True


The FileName is composed by concatenating the value from two controls and a date field.  In your case get the path from Currentdb.Name and concatenate it with the hard-coded file name you are using.

The TransferSpreadsheet exports a spreadsheet in the latest format from a query name stored in a control named cboQueries.  Your's will probably be hard-coded.
1
RachelAuthor Commented:
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.)
0
als315Commented:
Try this code:
Private Sub cmdImportUsers_Click()

Dim Response As Integer
Dim file As String, table As String, SQL As String
file = "tblPolicy.xls"
table = "tbl_Import"
SQL = "delete * from " & table

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.RunSQL SQL, dbFailOnError
    'DoCmd.OpenQuery "z_qry_Delete_z_tbl_TAM_Users"

    'file without column heads
    'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl_Import", CurrentProject.Path & "\" & file
    
    'file with column heads
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl_Import", CurrentProject.Path & "\" & file, True

    'DoCmd.RunSavedImportExport "Import-z_tblSourceEmployees"
    DoCmd.SetWarnings True
    MsgBox "Users Imported Successfully"
Else
    MsgBox "Users have not been imported"
End If

End Sub

Open in new window

Correct table name to real. I've supposed you file is with column heads. If no, use commented line
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.