External table is not in the expected format|VBA & Macros|Excel Forum|My Online Training Hub

Hi,

I am using the following code to retrieve data from a closed file (.xlsx format). Everything works fine for data retrieval and viewing.

Public Function PickData(oConn As Object, oRec As Object, sSQL As String) As Integer
    'connect to the DB, apply Query, (paste result in  sheet Search), return #result
    'connection DB----------------------------------------------------------------------------------
    oConn.Open CONNSTR_DB
 
    On Error Resume Next
    If oConn.State <> adStateOpen Then
        MsgBox "Error connecting to Database!"
        Exit Function
    End If
    On Error GoTo 0
 
    oRec.Open sSQL, oConn, adOpenStatic, adLockOptimistic, adCmdText
    PickData = oRec.RecordCount '#result
    'ClearSearch
    If PickData <> 0 Then shRecup.Range("M2").CopyFromRecordset oRec
    oRec.Close
    oConn.Close
    'deconnection-----------------------------------------------------------------------------------
End Function

----

 
CONNSTR_DB = "PROVIDER=MICROSOFT.ACE.OLEDB.12.0;" & _
                       "DATA SOURCE=" & DataPath & DataFile & ";" & _
                       "Extended Properties=""Excel 12.0;HDR=YES;"";"

However, when I insert a new line (when I do an UPDATE, everything works correctly), I get an error message 'External table is not in the expected format' and the code stops at the line 'oConn.Open CONNSTR_DB'.

I've checked the file and everything seems correct in terms of the data, same format etc.

Additional information:
If I pick up the code where it left off and continue via F8, everything works perfectly again. If I then go back to this function, no problem.

If you have any clues, I'd love to hear from you. Thanks in advance.