Automation error Unspecified error

[Suavesav]

I have the below code to extract a report from a work tool but I keep getting an error.

Sub GenerateReportFromSQLServer()
 
    ' Declare variables for the SQL Server connection
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connectionString As String
 
    ' Declare variables for the report
    Dim reportData As Variant
    Dim reportWorksheet As Worksheet
    Dim reportRange As Range
 
    ' Declare variables for the report filters
    Dim region As String
    Dim country As String
    Dim year As Integer
    Dim month As Integer
    Dim week As Integer
    Dim station As String
 
    ' Set the connection string to the SQL Server database
    connectionString = "Provider=SQLOLEDB;Data Source=somepath;Initial Catalog=DATABASENAME;User ID=;Password=;"
 
    ' Create a new connection object and open it
    Set cn = New ADODB.Connection
    cn.connectionString = connectionString
    cn.Open
 
    ' Prompt the user to enter the filter criteria
    Dim dateRange As String
    Dim profileName As String

    dateRange = InputBox("Enter Date Range:")
    profileName = InputBox("Enter Profile Name:")

    ' Build the SQL query based on the filter criteria entered by the user
    Dim sql As String
    sql = "SELECT * FROM TABLENAME"

    If dateRange <> "" Then
        If InStr(sql, "WHERE") = 0 Then
            sql = sql & " WHERE DateType = '" & dateRange & "'"
        Else
            sql = sql & " AND DateType = '" & dateRange & "'"
        End If
    End If

    If profileName <> "" Then
        If InStr(sql, "WHERE") = 0 Then
            sql = sql & " WHERE ProfileName = '" & profileName & "'"
        Else
            sql = sql & " AND ProfileName = '" & profileName & "'"
        End If
    End If

    ' Create a new recordset object and open it using the SQL query
    Set rs = New ADODB.Recordset
    rs.Open sql, cn
 
    ' Load the recordset data into a 2-dimensional array
    reportData = rs.GetRows
 
    ' Close the recordset and connection
    rs.Close
    cn.Close
 
    ' Set the report worksheet and range
    Set reportWorksheet = ThisWorkbook.Worksheets.Add
    reportWorksheet.Name = "Report"
    Set reportRange = reportWorksheet.Range("A1").Resize(UBound(reportData, 2) + 1, UBound(reportData, 1) + 1)
 
    ' Transpose the data and write it to the worksheet
    reportRange.Value = WorksheetFunction.Transpose(reportData)
 
End Sub