Import from named ranges in Excel workbook into Access and append to tables

Posted on 2012-09-20
Medium Priority
Last Modified: 2012-09-20

I would be grateful if you could you please explain what VBA script I could use to import data from named ranges in an Excel workbook and append to existing tables in an Access database.  Each range in the Excel workbook would correspond to a table in Access.

I would need to perform this import on several Excel workbooks which would have different filenames.

Thank you
Question by:alisonthom
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38417802
you can use this command line

docmd.transferspreadsheet acimport,8,"Nameoftable","c:\folder\myXl.xls",true,"NameOfRange"
LVL 31

Accepted Solution

Helen Feddema earned 1000 total points
ID: 38418434
Or the longer (but more comprehensible) named argument syntax:

DoCmd.TransferSpreadsheet transfertype:=acImport, _
   spreadsheettype:=acSpreadsheetTypeExcel9, _
   tablename:=strTable, _
   FileName:=strWorkbook, _ 
   hasfieldnames:=True, _

Open in new window

For the spreadsheettype value, here is a list from the Help topic:

Spreadsheettype values enum
The Help topic is a little out of date; there is also a new value for Office 2010, acSpreadsheetTypeExcel12Xml (Excel 2010 workbook).

Author Closing Comment

ID: 38418838
Thank you very much for the quick and detailed response!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

809 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