By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,961 Members | 2,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,961 IT Pros & Developers. It's quick & easy.

Importing Multiple Excel Files Into Access

P: 2
I have two files I need to append to an access database. These two files will be pulled from a folder on a continual basis. Once I append these two files I plan on deleting them from the folder. Then the process starts again where the folder will buildup with new files that I will need to append to the database once again.
Example: Student-TXCB01 needs to be appended to Student_Table_Import. Course-TXCB01 needs to be appended to Course_Table_Import. Student and Course will always be part of the filename. What appears after the hypen will change.
I want to build a button with code to do this process automatically.
Mar 16 '12 #1
Share this Question
Share on Google+
5 Replies

P: 759
Take a look here:
I think you will find almost all tools to develop your code.
If you have more specific question feel free to ask.
Mar 17 '12 #2

P: 2
Hi Mihail
I took a look at the fully customized export data from Access to Excel. It looks to me it is based on several sheets within the workbook. I want to import two excel files and append into two access tables. I need more guidance before I start adjusting code to meant my means. Appreciate all the help you can give me.
Mar 17 '12 #3

P: 759
Hi Jamhome !
I don't forget about you.
But is far beyond my English to explain you "how to".
Also, any language I use, is a lot to explain.
So I decide to design for you (and not only for you) a small wizard to do that job.
But I need more time to finish it.
So... stand by !
Mar 21 '12 #4

P: 759
I think you will find what you are looking for in post #16
Mar 22 '12 #5

Expert 5K+
P: 8,638
The Logic as I see it is quite simple.
  1. Use the Dir() Function recursively to return all Excel Files (*.xls) in a pre-determined Folder.
  2. Examine each File to see if it begins with either 'Student' or 'Course'.
  3. Perform the appropriate Import/Append Operation depending on the prior outcome.
  4. DELETE the Excel Fiels from the specified Folder.
  5. Base Code:
    Expand|Select|Wrap|Line Numbers
    1. Dim strTemp As String
    3. '***************************** USER DEFINED CONSTANTS *****************************
    4. Const conBASE_FOLDER As String = "C:\Test\"
    5. Const conFILESPEC As String = "*.xls"           'Only Excel Files
    6. '**********************************************************************************
    8. strTemp = Dir(conBASE_FOLDER & conFILESPEC)     'Produces ..Folder\*.FileSpec
    10. Do While strTemp <> vbNullString                'As long as FileNames are returned
    11.   If Left$(strTemp, 7) = "Student" Then
    12.     Debug.Print "Import/Append " & strTemp & " to Student_Table_Import"
    13.   ElseIf Left$(strTemp, 6) = "Course" Then
    14.     Debug.Print "Import/Append " & strTemp & " to Course_Table_Import"
    15.   Else
    16.     'Do nothing
    17.   End If
    18.     strTemp = Dir                                'Recursively call Dir() Function
    19. Loop
    21. Kill conBASE_FOLDER & conFILESPEC                'DELETE all Excel Files (*.xls)
  6. Sample OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. Import/Append Course-TXCB01.xlsx to Course_Table_Import
    2. Import/Append Student-TXCB01.xlsx to Student_Table_Import
Mar 22 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.