Results 1 to 5 of 5
  1. #1
    jyellis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    20

    Import VBA routine does not recognize the Excel File

    Using Access 2016:

    I have a form:
    Click image for larger version. 

Name:	browse.png 
Views:	14 
Size:	11.9 KB 
ID:	30250

    After I browse for the Excel file. Then I click "Import Excel" button



    I get the error: "The file you tried to import was not an Excel Spreadsheet"
    I have tried numerous Excel files both xls and xlsx.

    Code:
    Private Sub btnImportSpreadsheet_Click()
    Dim fso As New FileSystemObject
    If Nz(Me.txtfilename, "") = "" Then
        MsgBox "Please select a file!"
        Exit Sub
    End If
    
    If fso.FileExists(Nz(Me.txtfilename, "")) Then
        ImportExcel.importExcelSpreadsheet Me.txtfilename, "tbl_raw_verizon"
        Else
        MsgBox "File Not Found"
    End If
    
    End Sub
    
    
    'The above code calls this:
    
    Public Sub importExcelSpreadsheet(filename As String, tablename As String)
    On Error GoTo badformat
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tablename, filename, True
    Exit Sub
    
    badformat:
     MsgBox "The file you tried to import was not an Excel Spreadsheet"
    End Sub
    This is the references I have loaded:
    Click image for larger version. 

Name:	references.png 
Views:	14 
Size:	7.0 KB 
ID:	30251

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Given your error handling, it would be more accurate to say that the file isn't being imported without error. Try commenting out the "On Error..." line temporarily and see what error you get.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might also try using "acSpreadsheetTypeExcel12Xml" instead of "acSpreadsheetTypeExcel12".

    (I remember reading somewhere that "acSpreadsheetTypeExcel12" is 2007 format, and "acSpreadsheetTypeExcel12Xlm" is 2010 format.)

  4. #4
    jyellis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    20

    Post SOLVED - Thanks

    Changing it to "acSpreadsheetTypeExcel12Xml" worked!

    Thank you, SSANFU - Steve

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You're welcome...

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

Similar Threads

  1. Replies: 34
    Last Post: 08-09-2017, 09:57 AM
  2. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. Import Excel file based on a date and time in file name
    By nhylan in forum Import/Export Data
    Replies: 4
    Last Post: 04-16-2013, 03:26 PM
  5. Importing CSV file into 2010/creating save import routine
    By rbtrout in forum Import/Export Data
    Replies: 5
    Last Post: 08-05-2011, 08:54 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