Option Explicit

Public Sub ExecStoredProc(dDate As Date)
    Const strServerName$ = "ServerName", _
          strDBName$ = "DBName", _
          strUser$ = "User", strPass$ = "Pass"
    Const adAsyncExecute& = 16, _
          adStateOpen& = 1, _
          adStateExecuting& = 4
    With CreateObject("ADODB.Connection")
        .Open Join(Array( _
            "DRIVER=SQL Server", _
            "SERVER=" & strServerName, _
            "UID=" & strUser, _
            "password=" & strPass, _
            "APP=2013 Microsoft Office system", _
            "WSID=" & Environ$("computername"), _
            "DATABASE=" & strDBName), ";")
        .Execute "exec МояПроцедура '" & Format(dDate, "yyyy-mm-dd") & "'", , _
                 adAsyncExecute
        Do While .State = (adStateOpen Or adStateExecuting)
            DoEvents
        Loop
        .Close
    End With
    'ThisWorkbook.RefreshAll 'обновление всех подключений
End Sub

Option Explicit

Private Sub CommandButton1_Click()
    ExecStoredProc [A1]
End Sub