Access VBA Import table

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
rpreissAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
rpreissAuthor Commented:
how do you use the transferspreadsheet method and the DoCmd.  Unfortunaetly I am very new to this, especially with Access.

Thanks,

Artie
0
 
Patrick MatthewsCommented:
Artie,

Go to the VB Editor, and do a search on TransferSpreadsheet in the Help.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
frankyteeCommented:
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
 
rpreissAuthor Commented:
How would I construct this loop to go through all of the sheets in the workbook?  I am new to VBA.
0
 
frankyteeConnect With a Mentor Commented:
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
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
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.