Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Unanswered: Importing Excel to Access Table

    Team,
    I'm attempting to import data from an MS Excel spreadsheet. During the import process, the wizard is not allowing me to edit the field names, only the first one. Don't understand why I can't edit any other fields, or determine if I don't want to import other fields. Any suggestions as to why? Should I go about another way to import the Excel data? Looking for the easiest way to do this. It's not a lot of data (about 20 fields and 60 rows), but I will need to append to the table each month. It's a month production report saved as an Excel file. I would like to generate a report or two using Access. There is other data that will also be used.

    Don't know if this helps or not, but the report is from the Red Prairie LMS utility.

    Any help with this is very much appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I have never found a straight forward way around this. But, this idea works: Import the data just as it is, then write a query to take the data you want form this table and add it to the table where you will be reporting from. In the query you can take data from one field name and output it to the correct field name in the "report-from" table.

    Another idea would be to link the Excel spreadsheet into Access (as a linked table), then using a query you could either report directly from the linked table, or if you are going to keep the data in Access, then write the data from the linked table to your Access table, again, with a query as in the first idea.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    There are two other ways I have used. First, open the file in Excel then Save As a comma delimited file. Then in Access you will have much more control of the field names and data to import using a File Specification.

    Or, you can open the Excel spreadsheet using VBA and export the data to a delimited file and then import the delimited file. I took this approach to completely automate importing Excel spreadsheets. But it is not easy, and it is not fast.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •