locked
Connection string won't open XLSX files RRS feed

  • Question

  • I'm running into a confounding error.  I'm trying to open an XLSX file in Excel 2010 using as An  ADO connection.  When I attempt it, it just throws an error stating "External Table is not in the expected format".  It will connect to XLS files though, which is very strange because i'm using the ACE provider and I"m declaring it as Excel 12.0.  Here's the code:

    Set ConnFile = New ADODB.Connection
    ConnFile.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Filename & ";Extended Properties = ""Excel 12.0;HDR=NO;IMEX=1"""
    ConnFile.Open
    Everything I've looked at says that connection string should be successful.  What am I doing wrong here? 

    Friday, May 2, 2014 12:47 AM

All replies

  • Instead of 

    ConnFile.ConnectionString "connection-string"

    try

    ConnFile.Open "connection-string"

    Friday, May 2, 2014 7:15 AM
  • The result is the same if I do
    ConnFile.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Filename  & ";Extended Properties = ""Excel 12.0 Macro;HDR=NO;IMEX=1"""

    "External table is not in the expected format"
    Friday, May 2, 2014 1:04 PM
  • It works fine for me with your string doing ConnFile.Open, and FileName referring to a valid file of course. 

    What version of Office and OS are you using? If 2003 you'll probably need a string that relates to an earlier different driver, does this string work for you (ensure Filename refers to the file)

    str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Filename & ";Extended Properties = ""Excel 8.0;HDR=NO;IMEX=1"""

    ConnFile.Open str


    Friday, May 2, 2014 2:40 PM
  • I'm using Office 2010, Windows 7 32Bit.  Also, in case this matters I'm using the "Microsoft ActiveX Data Objects 6.1 Library" and "Microsoft ActiveX Data Objects Recordset 6.0 Library" References.

    The string you've provided above fails the same way.  "External table is not in the expect format."

    Friday, May 2, 2014 2:54 PM