Results 1 to 6 of 6
  1. #1
    Dave_D's Avatar
    Dave_D is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    67

    Runtime error 3274 - External table is not in the expected format

    set oxl = CreateObject("Excel Application")
    oxl.visible
    Set owb = oxl.workbooks.open(filename:=rst.fields("full_path "), readonly:=True, Password:="ops"

    To this point, the file opens just fine
    Next line of code

    Docmd.Transfersheet acimport, acspreadsheetTypeExcel12, "mytable", rst.full_path, True, "A:AC"
    is the line with the error.

    If I remove the password from the file and ONLY execute the Transfersheet statement (not opening the file), it works fine. The extension is .xlsx and I've tried changing it to be .csv (same error) as well as changing acSpreadSheetTypeExcel12 to 12Xml(same error). Access version is professional Plus 2016.

    Suggestions!


    Thanks!

  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
    Why are you opening Excel anyway? Just do the import.

    Only need to open Excel if using automation code, not TransferSpreadsheet.
    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
    Dave_D's Avatar
    Dave_D is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2015
    Posts
    67
    If I just execute the import, I get the error.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try using sql - something like

    Code:
    dim sqlStr as String
    sqlStr="INSERT INTO myTable" & _
    " SELECT XL.*" & _
    " FROM (SELECT * FROM [A:AC] AS xlData IN '" & rst.fullpath & "'[Excel 12.0;HDR=yes;IMEX=0;PWD=Ops;ACCDB=Yes])  AS XL"
    
    debug.print SqlStr
    
    currentdb.execute sqlStr

  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
    Did not need to nest.
    Code:
    CurrentDb.Execute "INSERT INTO myTable SELECT * FROM [A:AC] AS xlData IN '" & rst!fullpath & "'[Excel 12.0;HDR=yes;IMEX=0;PWD=ops;ACCDB=Yes]"
    Table must already exist. If not, can use SELECT INTO.
    Code:
    CurrentDb.Execute "SELECT XL.* INTO test FROM (SELECT * FROM [A:AC] AS xlData IN '" rst!fullpath & "'[Excel 12.0;HDR=yes;IMEX=0;PWD=ops;ACCDB=Yes]) AS XL"
    Ooops, then I tested on password protected file and both fail.

    Research indicates your original workaround method is appropriate. http://accessblog.net/2005/04/how-to...ted-excel.html

    BUT, missing closing paren for the Set owb line.

    Also:

    "Excel.Application"

    oxl.Visible = True

    DoCmd.TransferSpreadsheet

    And I still get the same error.

    I tried with an xls file (type 9) and after a couple of "cannot decrypt" errors, I removed the ReadOnly parameter and it imported. Same goes for xlsx (type 12).
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Did not need to nest.
    agreed but there are times when you do, this is probably not one of them. I actually create this in a function which returns the sql string and it's just easier to use an alias

    and my bad on the password - didn't test it, just thought it would work

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

Similar Threads

  1. Replies: 4
    Last Post: 11-19-2018, 03:26 PM
  2. External Table is not in the expected format (error message)
    By rltidd27 in forum Import/Export Data
    Replies: 2
    Last Post: 03-13-2017, 08:29 PM
  3. Replies: 2
    Last Post: 08-28-2015, 01:45 AM
  4. Replies: 1
    Last Post: 02-04-2013, 05:52 PM
  5. Runtime Error 3061 Expected 3
    By kumail123 in forum Programming
    Replies: 1
    Last Post: 03-28-2012, 09:44 AM

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