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

Posted on 2012-09-20
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 119

    Expert Comment

    by:Rey Obrero
    you can use this command line

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

    Accepted Solution

    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

    Thank you very much for the quick and detailed response!

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    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…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now