I need help importing data in Access 2013
Hello Experts,
I need help with a feature I need implemented to an Access 2013 application.
I am developing a scheduling app. The staff members email me their availability on a spreadsheet.
I then save those spreadsheet to a specific folder in my C drive (C:\Schedules\).
I have a table in Access 2013 where I save the data in the spreadsheets into. This is tedious work.
I want to create a form in Access 2013 with just 1 button, that will read all the files in the C:\Schedules directory.
And import the information into my schedules table.
Image of what my C:\Schedules directory
Here is a sample of the information that the spreadsheets contain:
USER ID DATE_AVAILABLE EMAIL PHONE
100001 11/1/2014 me@test.com 111-112-2233
100001 11/2/2014 me@test.com 111-112-2233
100001 11/9/2014 me@test.com 111-112-2233
100001 11/12/2014 me@test.com 111-112-2233
100001 11/13/2014 me@test.com 111-112-2233
100001 11/14/2014 me@test.com 111-112-2233
My tblSchedules table in Access has the same fields:
USER ID DATE_AVAILABLE EMAIL PHONE
How can I do this? Thank you in advance for all of your help.
mrotor
I need help with a feature I need implemented to an Access 2013 application.
I am developing a scheduling app. The staff members email me their availability on a spreadsheet.
I then save those spreadsheet to a specific folder in my C drive (C:\Schedules\).
I have a table in Access 2013 where I save the data in the spreadsheets into. This is tedious work.
I want to create a form in Access 2013 with just 1 button, that will read all the files in the C:\Schedules directory.
And import the information into my schedules table.
Image of what my C:\Schedules directory
Here is a sample of the information that the spreadsheets contain:
USER ID DATE_AVAILABLE EMAIL PHONE
100001 11/1/2014 me@test.com 111-112-2233
100001 11/2/2014 me@test.com 111-112-2233
100001 11/9/2014 me@test.com 111-112-2233
100001 11/12/2014 me@test.com 111-112-2233
100001 11/13/2014 me@test.com 111-112-2233
100001 11/14/2014 me@test.com 111-112-2233
My tblSchedules table in Access has the same fields:
USER ID DATE_AVAILABLE EMAIL PHONE
How can I do this? Thank you in advance for all of your help.
mrotor
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use the DIR function to loop through a directory and get the contents:
Dim sFile As String
sFile = Dir("C:\Schedules", "*.xls")
Do Until Len(sFile) = 0
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table", "C:\Schedules\" & sfile
Dir
Loop
Personally, I'd import to a "staging" table first, then use VBA/SQL to move the new data into the "live" table. This would allow for you to insure you have clean data.
Also, if your file extension isn't ".xls", change the Dir command to reflect the correct extension.
Dim sFile As String
sFile = Dir("C:\Schedules", "*.xls")
Do Until Len(sFile) = 0
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table", "C:\Schedules\" & sfile
Dir
Loop
Personally, I'd import to a "staging" table first, then use VBA/SQL to move the new data into the "live" table. This would allow for you to insure you have clean data.
Also, if your file extension isn't ".xls", change the Dir command to reflect the correct extension.
Premium Content
You need an Expert Office subscription to comment.Start Today