Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Data Import Script from Excel to Access

Posted on 2011-03-01
13
Medium Priority
?
181 Views
Last Modified: 2012-05-11
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)
0
Comment
Question by:pwdells
[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
  • 5
  • 4
  • 4
13 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35007862
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
 
LVL 77

Expert Comment

by:peter57r
ID: 35007877
Can't see what the problem is with using multiple import commands, myself.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35007885
And no disrespect to cap1 but the code he's posted is just 4 import commands wrapped up in other code.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35007933
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
 

Author Comment

by:pwdells
ID: 35008035
The end-users are blocked out of those functions for the application.  The function has to be executed using one step.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35008137
then, try the codes i posted.
0
 

Author Comment

by:pwdells
ID: 35008150
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
 
LVL 77

Expert Comment

by:peter57r
ID: 35008154
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
 
LVL 77

Expert Comment

by:peter57r
ID: 35008168
or about  NOT using macro actions to do the same thing.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35008182
i really don't like when rules are being changed in the middle of the game.
0
 

Author Comment

by:pwdells
ID: 35008220
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
 

Author Comment

by:pwdells
ID: 35009110
It worked!  Thank you sooooo much Cap!
0
 

Author Closing Comment

by:pwdells
ID: 35009158
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question