ActiveWorkbook.PivotCaches.Create, Usar meus parametros meus parametros.

Question

   

Olá pessoal,

Criei uma macro e visualizei o código gerado, gostaria de saber em qual ponto eu posso inserir minha string de conexão ou o objeto de conexão.

Também gostaria de saber como faço para adicionar minha query, não posso deixar tudo cravado no código. 

Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("ConexaoBanco"), Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="PREVISÃO!R1C1", TableName:= _
        "Tabela dinâmica1", DefaultVersion:=xlPivotTableVersion12
    Cells(1, 1).Select
    With Sheets("PREVISÃO").PivotTables("Tabela dinâmica1").PivotFields("EMPREENDIMENTO")
        .Orientation = xlRowField
        .Position = 1
    End With
    With Sheets("PREVISÃO").PivotTables("Tabela dinâmica1").PivotFields("QUADRA")
        .Orientation = xlRowField
        .Position = 2
    End With
    With Sheets("PREVISÃO").PivotTables("Tabela dinâmica1").PivotFields("LOTE")
        .Orientation = xlRowField
        .Position = 3
    End With
     Sheets("PREVISÃO").PivotTables("Tabela dinâmica1").AddDataField Sheets("PREVISÃO"). _
        PivotTables("Tabela dinâmica1").PivotFields("VLRDEVIDO"), "Soma de VLRDEVIDO", _
        xlSum
    With Sheets("PREVISÃO").PivotTables("Tabela dinâmica1").PivotFields("DATAVENC")
        .Orientation = xlColumnField
        .Position = 1
    End With
    Sheets("PREVISÃO").PivotTables("Tabela dinâmica1").PivotCache.Refresh
End Sub

Obrigado

---

kaneda182

Answer 1

Esta parte do codigo talvez se refere a isso:

    ActiveWorkbook.Connections("ConexaoBanco"), 

Tentei depurar, colei este metodo no immediate window , mas infelizmente ele me retorna a "ConexaoBanco" ao invés do conteúdo desse metodo.

Ninguém sabe como fazer uma conexão para um banco de dados externo?

---

kaneda182

Answer 2

Encontrei neste site http://blogs.msdn.com/b/andreww/archive/2008/07/25/creating-a-pivottable-programmatically.aspx , uma forma de fazer isso em C#, alguém sabe como fazer em VBA?

string connection =

    @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=AdventureWorks";

string command =

    "SELECT * FROM [Sales].[vSalesPersonSalesByFiscalYears]";

Next, I added a new PivotCache to the PivotCaches collection in the active workbook, and set its data connection and SQL command properties:

Excel.PivotCache pivotCache =

    this.Application.ActiveWorkbook.PivotCaches().Add(

    Excel.XlPivotTableSourceType.xlExternal, missing);

pivotCache.Connection = connection;

pivotCache.MaintainConnection = true;

pivotCache.CommandText = command;

pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;

I can then add a new PivotTable to the worksheet, based on the PivotCache I’ve just configured:

Excel.Worksheet sheet = (Excel.Worksheet)this.Application.ActiveSheet;

Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(missing);

Excel.PivotTable pivotTable = pivotTables.Add(

    pivotCache, this.Application.ActiveCell, "PivotTable1",      missing, missing);

Then, set the PivotTable to use the pivot table stencil outline instead of the default 2x2 cell grid, and format it with grey alternating row shading:

pivotTable.SmallGrid = false;

pivotTable.ShowTableStyleRowStripes = true;

pivotTable.TableStyle2 = "PivotStyleLight1";

Set up the SalesTerritory field as the page field, and FullName as the row field:

Excel.PivotField pageField =       (Excel.PivotField)pivotTable.PivotFields("SalesTerritory");

pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;

Excel.PivotField rowField =       (Excel.PivotField)pivotTable.PivotFields("FullName");

rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

Add a data field for the sales for 2004:

pivotTable.AddDataField(

    pivotTable.PivotFields("2004"), "Sum of 2004", Excel.XlConsolidationFunction.xlSum);

---

kaneda182

Answer 3

Kaneda,

Para visualizar todas as conexões de sua pasta de trabalho, você pode usar o código:

Sub VerConexões()
    Dim wbcnn As WorkbookConnection
    For Each wbcnn In ThisWorkbook.Connections
        MsgBox wbcnn.Name
    Next wbcnn
End Sub

No seu caso, parece que a conexão que sua tabela dinâmica usa se chama ConexaoBanco. Então, adapte o código abaixo:

Sub AlterarCadeia()
    Dim wbcnn As WorkbookConnection
    
    Set wbcnn = ThisWorkbook.Connections("Database1")
    
    'Para ver a conexão:
    MsgBox wbcnn.OLEDBConnection.Connection
    'ou, se a conexão for numa base de dados ODBC:
    'MsgBox wbcnn.ODBCConnection.Connection
    
    'Para alterar a conexão:
    'wbcnn.OLEDBConnection.Connection = "OLEDB;Provider=Microsoft.ACE.....
End Sub

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br