title: Run Time Error: External table is not in the expected format
body: <p>I am trying to pull data from a <strong><em>Shared Drive</em></strong> without opening the file.<br>
  I used <em>ADO</em> so I can manipulate the resulting table easily since I just need specific fields.<br>
  Rather than doing the filter and copy routine, <em>ADO</em> is preferable since I can use <em>sql query</em> to get what I want.<br></p>
  
  <p>The line: <code>rec.Open sqlStr, con, adOpenStatic, adLockReadOnly</code></p>
  
  <p>throws the error</p>
  
  <blockquote>
    <p>Run-time error ...:<br>
    External table is not in the expected format.</p>
  </blockquote>
  
  <p><img src="https://i.stack.imgur.com/4VQ2I.png" alt="enter image description here"></p>
  
  <p>The file is in <em>.xls</em> format and it is included in the <em>Trust Center</em> default setting of blocked files (files that will be opened in Protected View).</p>
  
  <p>Out of curiosity, I temporarily disabled the file blocking but the same error occurred.</p>
  
  <p><strong><em>When I open the file and run the code, it retrieves the data.</em></strong></p>
  
  <p>How can I make this work without opening the file?</p>
  
  <p><strong>Code I use:</strong></p>
  
  <pre><code>Sub stancial()
      Dim sPath As String, fName As String
  
      sPath = "P:\Folder\": fName = "Report.xls"
  
      Dim con As ADODB.Connection: Set con = New ADODB.Connection
      Dim rec As ADODB.Recordset: Set rec = New ADODB.Recordset
  
      Dim DataSource As String
      DataSource = sPath &amp; fName
  
      con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
              "Data Source=" &amp; DataSource &amp; ";" &amp; _
              "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  
      Dim cat As ADOX.Catalog: Set cat = New ADOX.Catalog
      Set cat.ActiveConnection = con
  
      Dim shName As String
      shName = Replace(cat.Tables(0).Name, "'", "")
  
      Dim sqlStr As String
      sqlStr = "SELECT * FROM [" &amp; shName &amp; "];"
  
      rec.Open sqlStr, con, adOpenStatic, adLockReadOnly
      Sheet1.Range("A2").CopyFromRecordset rec
  
      rec.Close
      con.Close
      Set cat = Nothing
      Set rec = Nothing
      Set con = Nothing
  End Sub
  </code></pre>
  
  <p><strong>Edit1:</strong></p>
  
  <p>I also tried this:</p>
  
  <pre><code>    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" &amp; _
              "Data Source=" &amp; DataSource &amp; ";" &amp; _
              "Extended Properties=""Excel 8.0;HDR=YES"";"
  </code></pre>
  
  <p>and also this:</p>
  
  <pre><code>    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
              "Data Source=" &amp; DataSource &amp; ";" &amp; _
              "Extended Properties=""Excel 8.0;HDR=YES"";"
  </code></pre>
  
  <p>It still gives the error. I think is has something to do with opening the <em>Recordset</em> rather than the <em>Connection</em>.</p>