Search code examples
excelvbaconnection-string

Catastrophic Failure EXCEL VBA - error '-2147418113 (8000ffff)'

I am struggling to find relevant information on the

'run-time error '-2147418113 (8000ffff)' - Catastrophic Failure'

I am experiencing.

Sub GenerateAIA_Click()
Dim SQL_query, SQL_syntax, DB_path, setting_conn As String
Dim conn As New ADODB.Connection
Dim query_rslt As New ADODB.Recordset

Dim mth, mth_yr As Variant
Dim dt As Date
Dim i, bol As Integer
Dim temp1, temp2 As Variant

dt = Sheets("Main").Range("C4")
mth_yr = MonthName(Month(Sheets("Main").Range("I12")), False) & " " & Year(Sheets("Main").Range("I12"))

ThisWorkbook.Sheets("AIA").Select

DB_path = ThisWorkbook.FullName 'Refering the same workbook as Data Source
setting_conn = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DB_path & ";HDR=Yes';"
conn.Open setting_conn
SQL_syntax = "SELECT * FROM [Setup$]"   'Your SQL Statement (Table Name= Sheet Name=[Sheet1$])
query_rslt.Open SQL_syntax, conn

I have also noticed that this error is shown on the line

conn.Open setting_conn

I use excel 2016 and also my file format .xlsm
Anyone have idea why is this happening?

Solution

  • It seems your connection string has a problem. Here's how I got it to work:

    (First make sure to add a reference to the Microsoft Active-X Data Objects Library)

    enter image description here

    Sub test()
  Dim conn As New ADODB.Connection
  Dim rs As New ADODB.Recordset

  conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\PC\test.xlsm;Extended Properties=""Excel 12.0 Macro;HDR=NO"";"
  conn.Open

  rs.Open "SELECT * FROM [Sheet1$]", conn

  If Not rs.EOF Then
    MsgBox rs(0) ' display the value of the first field in the first row
  Else
    MsgBox "No records found."
  End If

  rs.Close
  conn.Close

End Sub

    So take my example, change the filename to your XLSM file, and the sheet name to your sheet name (with a $ added to the end of it)

    If your sheet has header names in the first now, use HDR=Yes, and if not, HDR=No