?
Solved

Access VBA Import table

Posted on 2007-09-28
8
Medium Priority
?
1,369 Views
Last Modified: 2013-11-27
I have a macro that is currently in an Excel Module that reformats excel sheets so they are importable into access.  How would I write code in access that will run the procedures that are currently in the excel module to reorganize the tables, but also import them into seperate tables for each sheet in the workbook?

Thanks,

Artie
0
Comment
Question by:rpreiss
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 19980955
Hello Artie,

To call the macros from Access:



Dim xlApp As Object, xlWb As Object
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\folder\subfolder\book.xls")
xlApp.Run "'" & xlWb.Name & "'!NameOfMacro"




Then, to load from Excel to Access, use the TransferSpreadsheet method of Access's DoCmd object.

Regards,

Patrick
0
 

Author Comment

by:rpreiss
ID: 19981042
how do you use the transferspreadsheet method and the DoCmd.  Unfortunaetly I am very new to this, especially with Access.

Thanks,

Artie
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19981062
Artie,

Go to the VB Editor, and do a search on TransferSpreadsheet in the Help.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 19

Expert Comment

by:frankytee
ID: 19983892
eg below, replace names accordingly
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "AccessTableName", "c:\excelfile.xls", True  'true for has field names

put it inside your loop through your worksheets etc
0
 

Author Comment

by:rpreiss
ID: 19988577
How would I construct this loop to go through all of the sheets in the workbook?  I am new to VBA.
0
 
LVL 19

Assisted Solution

by:frankytee
frankytee earned 1000 total points
ID: 19989369
eg below, replace names accordingly

    Dim xlWb As Excel.Workbook, xlWs As Excel.Worksheet
    Dim xlApp As Excel.Application
     
    Set xlApp = New Excel.Application
    Set xlWb = xlApp.Workbooks.Open("c:\whateverfolder\whatever.xls")  'whatever file
    For Each xlWs In xlWb.Worksheets
        '.... do your stuff
       
    Next xlWs
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20324456
Forced accept.

Computer101
EE Admin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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 …

862 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