Results 1 to 5 of 5
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Importing an excel file through vba

    I work in a school and the database we have exports the following into an excel file automatically.

    Firstname text
    Lastname text
    Year (as year 8) text
    Group text

    I'd like to import it into a table using vba using docmd but the table set up is:

    Firstname text
    Lastname text


    Year (as 8) integer
    Group text

    Can anyone advise how to get the data in the right format into the table. I'd like the whole process to be by vba.

    Many thanks

    Andy



    Sent from my iPhone using Tapatalk

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Import it into a temporary table, then run a query to add it to the table. Not sure what you mean by "as 8" but to convert a field to integer use
    CInt(Year)

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Aha. Great idea


    Sent from my iPhone using Tapatalk

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be aware, shouldn't use "Year" as a field name. It is a reserved word as well as a built in function. See http://allenbrowne.com/AppIssueBadWord.html

    In addition, "year" is not very descriptive... "Year" of what??? Birth year? year of last payment? School year?

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Apologies. I forgot the fieldname is yrgp. As in school year


    Sent from my iPhone using Tapatalk

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

Similar Threads

  1. Importing an updated file from Excel to DB
    By Radtastic10 in forum Access
    Replies: 2
    Last Post: 07-07-2016, 12:45 PM
  2. Importing Excel file to access
    By bambereczek in forum Access
    Replies: 1
    Last Post: 09-10-2012, 06:38 AM
  3. Importing .CSV file to excel
    By herbc0704 in forum Import/Export Data
    Replies: 1
    Last Post: 05-17-2012, 05:25 PM
  4. importing data from an excel file
    By slimjen in forum Access
    Replies: 3
    Last Post: 09-21-2011, 12:38 PM
  5. Importing an Excel file
    By bvtterflygirl in forum Import/Export Data
    Replies: 5
    Last Post: 02-24-2011, 11:54 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
  •  
Other Forums: Microsoft Office Forums