ADO Connection to Current Workbook - Error when Read Only

[mgmirvine]

All,

Firstly, thank you for any help you can provide.

I have a workbook test.xlsm, which has two sheets, Data and Report. Then, I have some VBA that creates an ADO Connection to Data in order to run SQL to pull some data from Data into Report, see below. When I open the workbook it runs just fine. However, if I set the file property to read-only, I get an error: "Run-time error '-2147467259 (80004005)': External table is not in the expected format." ... The reason it needs to work in Read-Only format, is this will be available to the end user on SharePoint, where they will open the file as read only just to view the report.

Dim MyConnection As Connection
Dim Myrecordset As Recordset
Dim MyWorkbook As String
 
Set MyConnection = New Connection
Set Myrecordset = New Recordset
  
'Identify the workbook you are referencing
    MyWorkbook = Application.ThisWorkbook.FullName
 
'Open connection to the workbook
MyConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & MyWorkbook & ";" & _
                  "Extended Properties=Excel 12.0;IMEX=1;" & _
                  "Persist Security Info=False"

Again, thank you!