Update dynamic table with connection to a Stored Procedure using Macro VBA

Question

I aim to bring the data from a Stored Procedure to a spreadsheet in Excel. This Stored Procedure is using two parameters for the query.

Excel 2013

SQL Server

What do I have

I set up a macro that queries the database, updates a table with the query result (Data) and then updates the dynamic table (Viewhistorico). Follows the code:

Sub Atualizar()
 Dim lDataIni As String
 Dim lDataFim As String
'Obtem os dados do parêmetro para consulta
 lDataIni = "'" & Format(Sheets("ViewHistorico").Range("C1").Value, "yyyyMMdd HH:mm:SS") & "'"
 lDataFim = "'" & Format(Sheets("ViewHistorico").Range("C2").Value, "yyyyMMdd 23:59:59") & "'"

    Sheets("Dados").Select
    'executa a consulta na planilha Dados para popular a tabela
    With ActiveWorkbook.Connections("Query from dbDW").ODBCConnection
        .BackgroundQuery = False 'False para forçar a espera da atualização do PowerPivot
        .CommandText = Array("EXEC SpHistoricoCobrancaProdutor " & lDataIni & " , " & lDataFim)
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=dbDW;UID=efritsch;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=C3" _
        ), Array("1089;DATABASE=DW;"))
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Query from dbDW")
        .Name = "Query from dbDW"
        .Description = ""
    End With

    ActiveWorkbook.RefreshAll

    'Atualiza o PowerPivot com base na planilha Dados que foi atualizadas
    Sheets("ViewHistorico").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

    MsgBox ("Atualizado com sucesso!")

End Sub

What I want

I would like when creating a macro button I can automatically update my dynamic table without the need to have another table. At this point that I’m not getting, I’m not able to develop a VBA code that allows updating only the dynamic table with the data of the Stored Procedure query.

Answer 1

Instead of updating a table with the database connection, use the reference Microsoft Activex Data Objects x.x Library and updates the dynamic table directly.

Small example of generic code without error checking:

Private Sub Macro()
  With New ADODB.Connection
    .Open "ODBC;DRIVER=SQL Server"
    With .Execute("SELECT * FROM NomeTabela LIMIT 5")
      For i = 0 To .Fields.Count - 1
        Cells(1, i + 1).Value2 = .Fields.Item(i).Name
      Next
      Dim Linha As Long: Linha = 1
      While Not .EOF
        Linha = Linha + 1
        For i = 0 To .Fields.Count - 1
          Cells(Linha, i + 1).Value2 = .Fields.Item(i)
        Next
        .MoveNext
      Wend
    End With
    .Close
  End With
End Sub