Expermente usar a propriedade State.
Se o valor for 1 entao conecta outro banco
Teria que analizar seu coidigo por inteiro.
Mas seria mais ou menos assim:
Ex.:

Dim connection As ADODB.connection
Dim recordSet As ADODB.recordSet
Dim stringSQL As String

Const stADO As String = "Provider=SQLOLEDB;Server=MSSQLMATRIZ;" & _
"Database=CONTROLRISK;" & _
"User Id=USERCONS;Password=12345;" 'SERVIDOR MATRIZ


If Not (recordSet Is Nothing) Then
  If (recordSet.State And adStateOpen) = adStateOpen Then rs.Close
  Set rs = Nothing
  
Else

Const stADO As String = "Provider=SQLOLEDB;Server=MSSQLMATRIZ;" & _
"Database=SEUOUTROBANCO;" & _
"User Id=USERCONS;Password=12345;" 'SERVIDOR MATRIZ
End If

Basole, não deu certo, apresenta a mensagem de erro:
Compile Error:
Duplicate declaration in current scope

abaixo o código completo incluído o código que vc propôs:

Sub Importa_Boletas()
    
    On Error GoTo ErrHand
    
    Dim connection As ADODB.connection
    Dim recordSet As ADODB.recordSet
    Dim stringSQL As String
    Dim day As Date
    
    Cells(18, 2) = Now
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    day = Cells(5, 4)
    
    Sheets("Boletas_Trades").Select
    ActiveSheet.AutoFilterMode = False
    
    Const stADO As String = "Provider=SQLOLEDB;Server=MSSQLMATRIZ;" & _
    "Database=CONTROLRISK;" & _
    "User Id=USERCONS;Password=12345;"
    
    If Not (recordSet Is Nothing) Then
    If (recordSet.State And adStateOpen) = adStateOpen Then rs.Close
    Set rs = Nothing
  
    Else

    Const stADO As String = "Provider=SQLOLEDB;Server=MSSQLCONTIGENCIA;" & _
    "Database=CONTROLRISK;" & _
    "User Id=USERCONS;Password=12345;" 
    End If
    
    
    stringSQL = "SELECT  " _
    & "[ID]" & ",[NomeTipoContrato]" & ",[NomeContrato]" & ",[NomeCarteira]" & ",[Quantidade]" & ",[Natureza]" & ",[Preco]" & ",[PrecoMercado]" _
    & ",[Taxa]" & ",[DataOperacao]" & ",[DataAfter]" & ",[DataTermo]" & ",[Deliverable]" & ",[DataLiquidacao]" & ",[DataCarregamento]" & ",[Simulacao]" _
    & ",[Estrategia]" & ",[Contraparte]" & ",[Corretora]" & ",[Custodiante]" & ",[Operador]" & ",[VencimentoOperacao]" & ",[VencimentoContrato]" _
    & ",[TaxaAtiva]" & ",[TaxaPassiva]" & ",[DataBaseJuros]" & ",[Prazo]" & ",[CotacaoInicial]" & ",[Strike]" & ",[DataFixing]" _
    & ",[IDExterno]" _
    & " FROM [rcPhoenixBRPartners314].[dbo].[vwOperacoes]" _
    & " WHERE Simulacao = 0 AND DataOperacao = '" & Format(day, "yyyy-mm-dd") & "' AND (CodigoCarteira = 'PRF_BR_Partners_Capital' OR CodigoCarteira = 'PRF_TOTAL_FIM' OR CodigoCarteira =  'PRF_BR_INN_(Carteira)' ) " _
    & " ORDER by [DataOperacao]" & ",[NomeCarteira]" & ",[NomeTipoContrato]" & ",[DataLiquidacao]"
    Set connection = New ADODB.connection

    Range("A5:AE100000").Select
    Selection.ClearContents

    connection.CursorLocation = adUseClient
    connection.Open stADO
    connection.CommandTimeout = 0
    Set recordSet = connection.Execute(stringSQL)
       
    Dim i As Integer
    i = 5
    Dim j As Integer
    'Printing lines
    
    While recordSet.EOF <> True
        For j = 0 To 30
            If (i = 5) Then
                Cells(i - 1, j + 1) = recordSet(j).Name
                Cells(i, j + 1) = recordSet(j)
            Else
                Cells(i, j + 1) = recordSet(j)
            End If

        Next
        i = i + 1
        recordSet.MoveNext
    Wend
    
    Range("A1").Select
    
    Sheets("Main").Select
    Range("B8").Select
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    mensagem = MsgBox("Processo Finalizado", vbDefaultButton4, "Fixing")
    
Exit Sub

ErrHand:
    MsgBox (Err.Description)
    
End Sub