Import excel column range into access table fields based on key field

Posted on 2012-09-04
Last Modified: 2012-10-02
I'm trying to bring data into an access table that appends to existing records i..e adds new field values to existing records rather than adding new records.

I'm using access 2010 and have attempted Import Export Spreadsheet macro version but without success. When I run the macro teh timer spins but no data transfers.

Any help - gratefully received. Just need some tips on how to go about validating what I have set up for the import.


Question by:Needy11
    LVL 119

    Expert Comment

    by:Rey Obrero
    this will import the excel records from sheet1 columns B1 to F100

    docmd.transferspreadsheet acimport,10,"tableName","c:\folderName\myExcel.xlsx",true,"Sheet1!B1:F100"
    LVL 39

    Expert Comment

    May be you can show sample of source data and expected result? Do you like to add columns to existing table?

    Author Comment

    Hi, Yes I want to add columns to existing data records. In the attached sample its the online time, offline time and total time columns that need to be added. is there a way of specifying which field\column each will go into?
    LVL 39

    Accepted Solution

    I prefer to link excel tables to DB. In this case you can modify existing table at first and then use query and add necessary information

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    737 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