Results 1 to 3 of 3

Importing only parts of 3 Excel sheets into a pre-designed Access Database - Can it be done?

  1. #1
    Researcher is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Posts
    1

    Importing only parts of 3 Excel sheets into a pre-designed Access Database - Can it be done?


    Dear All,

    I have designed a database in Access and received 3 Excel sheets from other sources. I want to import data from these Excel sheets (i.e. columns) into some fields in Access which are present in each sheet. Once I have done this, I will use my form to complete the dataset.

    This can be done by cutting and pasting but can this be done in another way (to avoid errors?). I am using Microsoft Access 2007 but can use more modern versions.

    Thanks for your help,

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,050
    the standard access import (transferspreadsheet) imports all fields ,all records from the excel sheet.

    if you want ALL records, from SOME fields:
    link the excel sheet as a table, then run an append query for only the fields you want.

    if you want SOME records, from SOME fields:
    (like prev step) link the excel sheet as a table, then run an append query for only the fields you want using CRITERIA to filter records

    if you want SOME records, from SOME fields, NO key criteria: (hardest)
    then youd have to write an excel macro, to chop up the data to whats needed, save, then import transferspreadsheet.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,095
    Note that when importing Excel files, instead of importing the whole sheet, you can import Named Ranges.
    So. if you go into Excel and name the range you want to import, you can limit it to just import those records.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-26-2016, 05:42 AM
  2. Replies: 4
    Last Post: 07-29-2014, 05:18 AM
  3. Replies: 1
    Last Post: 05-28-2014, 09:59 PM
  4. Importing data into Access database from Excel textboxes
    By Sambora in forum Import/Export Data
    Replies: 3
    Last Post: 05-06-2013, 01:27 PM
  5. Replies: 2
    Last Post: 01-14-2013, 10:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums