Data Import Script from Excel to Access

Is there a way to build a script in access to import all the pages in a workbook, into separate tables in Access?  For instance, the pages in Excel would be Sales, Inventory, Returns, Demand Forecast.  Then import those pages into their respective tables, tblSales, tblInventory, tblReturns, tblDemand.

How do I do that without running 4 separate import processes?  In actuality, the above example is just a sample of what would have to be completed each week.  We actually have about 25 workbook pages going into Access.

Thank you!  :0)
pwdellsAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
test this codes



Sub ImportAllSheets()
Dim objXL As Object
Dim sTable, sFile As String, xlPath As String, i As Integer
sFile = "Test.xls"
xlPath = "J:\"

Set objXL = CreateObject("Excel.Application")
    objXL.Workbooks.Open xlPath & sFile, , True
    With objXL
        For i = 1 To .workSheets.Count
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
             "tbl" & .workSheets(i).Name, xlPath & sFile, True, .workSheets(i).Name & "!"
        Next
    End With
    objXL.Quit
    Set objXL = Nothing

End Sub

Open in new window

0
 
peter57rCommented:
Can't see what the problem is with using multiple import commands, myself.
0
 
peter57rCommented:
And no disrespect to cap1 but the code he's posted is just 4 import commands wrapped up in other code.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Rey Obrero (Capricorn1)Commented:
i know, but if you have 20 woksheets now and 25 worksheets on the next , how are you going to automate this?

going back to your codes and adding or commenting the import line commands?
0
 
pwdellsAuthor Commented:
The end-users are blocked out of those functions for the application.  The function has to be executed using one step.
0
 
Rey Obrero (Capricorn1)Commented:
then, try the codes i posted.
0
 
pwdellsAuthor Commented:
Often there are functions in a process that may come easy to you or me, but not others.  Historically, serious mistakes have been made due to repetition and confusion.  If this automation process works, we may end up importing 50 tables.  This is the only way the client is able to replicate with its customer's sales database, which is locked down considerably.  The only way to import data is via .xlsx files or PDF.  No ODBC is permitted.
0
 
peter57rCommented:
I don't have any argument over your code cap.  It's what I would do.

I was commenting on the requirement...
"How do I do that without running 4 separate import processes?"

Even in a loop each import process is separate from each other import process.

It now appears that the real issue is about using macro actions to do the same thing.
0
 
peter57rCommented:
or about  NOT using macro actions to do the same thing.
0
 
Rey Obrero (Capricorn1)Commented:
i really don't like when rules are being changed in the middle of the game.
0
 
pwdellsAuthor Commented:
What do you mean by "rules are being changed in the middle of the game"???  My request is still the same and currently, I am attempting to implement it within my environment.    :*?
0
 
pwdellsAuthor Commented:
It worked!  Thank you sooooo much Cap!
0
 
pwdellsAuthor Commented:
I made a few changes to the variables, but that is always to be expected.  Once I was able to get the script implemented in my environment, it worked without a hitch.  I am so very thankful to Cap for helping me with this task!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.