We help IT Professionals succeed at work.

Access VBA Import table

1,384 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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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

Thanks,

Artie
CERTIFIED EXPERT
Top Expert 2010

Commented:
Artie,

Go to the VB Editor, and do a search on TransferSpreadsheet in the Help.
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

Author

Commented:
How would I construct this loop to go through all of the sheets in the workbook?  I am new to VBA.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Forced accept.

Computer101
EE Admin
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.