Results 1 to 7 of 7
  1. #1
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58

    Import Excel files into Access that have xls and xlsx extenstions

    What is the best way to reference an Excel file to import where the extension could be .xlsx or .xls for a file.
    Also... how can I replace the current table with the new imported data.
    Here is some code I am trying to use...


    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _


    "NewSBACMath_5", CurrentProject.Path & Chr(92) & strSBACMath & ".xls & *", True

    Thanks in advance for the help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, I expect you have to know what the file extension is. AFAIK, filename must be fully specified and wildcard won't work. You tried it? What happened?


    For ideas on import methods, review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jdashm is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2016
    Posts
    58

    Import Excel To Access

    Thanks for the quick response and for confirming this with me.

    Quote Originally Posted by June7 View Post
    Well, I expect you have to know what the file extension is. AFAIK, filename must be fully specified and wildcard won't work. You tried it? What happened?


    For ideas on import methods, review http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

  4. #4
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Put in an error check routine for File Not Found; on the first instance change the ending from .xls to .xlsx
    e.g.
    Sub MyImportSub(strSBACMath as string)
    On Error goto err_Steps
    dim error_count_var as integer
    dim strFilePath, strFileName, strFileSuffix, str_SourceFile as string

    error_count_var=0

    strFilePath = currentproject.path (I didn't know this command! Will save me a lot of time!
    strFileName = strSBACMath
    strFileSuffix = ".xls"

    str_SourceFile = strFilePath & strFileName & strFileSuffix

    step_tryload:
    docmd.transferspreadsheet str_SourcFile etc.
    goto fini

    tough_luck_filenotfound:
    msgbox(("Sorry, file " & str_SourceFile & " was not found."),vbOk)

    fini:
    exit sub

    err_steps:
    case select err.number
    case [insert file not found error number]
    error_count_value=error_count_value+1
    if error_count_value>1 then goto tough_luck_filenotfound
    else
    str_SourceFile=str_SourceFile & "x"
    resume step_tryload

    case else
    msgbox( ("ERROR! " & err.number & & " - " & err.description),vbCrticial)

    resume fini

    end sub

    This code is untested but I have used variations before.
    Last edited by Peter M; 03-15-2018 at 02:40 PM. Reason: typo and missing a step

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ah, nice idea. Or test the file exists, like:

    strFileSuffix = IIf(Dir(strFilePath & strFileName & ".xls") <> "", ".xls", ".xlsx")

    This assumes a file with either extension exists. If there is possibility it won't then write code to deal with before it generates a runtime error that has to be dealt with by error handler.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Quote Originally Posted by June7 View Post
    Ah, nice idea. Or test the file exists, like:

    strFileSuffix = IIf(Dir(strFilePath & strFileName & ".xls") <> "", ".xls", ".xlsx")

    This assumes a file with either extension exists. If there is possibility it won't then write code to deal with before it generates a runtime error that has to be dealt with by error handler.
    That is much cleaner! I like it.

  7. #7
    AdrianG001 is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2018
    Posts
    9
    Hey,

    Here is the answer to your query. Just refer the below guide

    support.office.com/en-us/article/exchange-copy-import-export-data-between-excel-and-access-409c27cc-c69d-461d-a74c-35392a68ed53

    Adrian Gates
    Sr. Cloud Expert - Apps4Rent

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

Similar Threads

  1. Replies: 3
    Last Post: 01-31-2016, 06:14 PM
  2. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  3. Replies: 1
    Last Post: 06-27-2012, 07:39 AM
  4. import excel files into access automatically
    By jstei012 in forum Import/Export Data
    Replies: 1
    Last Post: 12-19-2011, 04:12 PM
  5. Replies: 3
    Last Post: 10-06-2011, 06:53 AM

Tags for this Thread

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