Update PivotTable with connection to a Stored Procedure using VBA Macro

Question

Update PivotTable with connection to a Stored Procedure using VBA Macro

Navigation

#1 by (1 votes)

0

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

Excel 2013

SQL Server

What I have

I mounted a macro that queries the database, updates a table with the result of the query (Data) and then updates the PivotTable (ViewHistorico). Here is 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 PivotTable without having to have another table. At this point I'm not getting it, I'm not able to develop a VBA code that makes it possible to update only the PivotTable with Stored Procedure query data.

excel excel-vba

asked by anonymous 09.07.2018 / 16:49

1 answer

How to check parentheses of an algebraic expression? Check password hash at login (password_hash)

score 1 · Answer 1

Instead of updating a table with the connection to the database, use the # and updates the PivotTable directly.

Small generic code sample with no 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