Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
Course Of The Month
8 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
I need help importing data in Access 2013
Posted on 2014-11-21
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
3 Comments
Active today
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.
This l…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month8 days, 5 hours left to enroll
Earn Certification
CompTIA IT Fundamentals
$49.00$44.10
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.