none
ActiveWorkbook.PivotCaches.Create, Usar meus parametros meus parametros. RRS feed

  • Pergunta

  •    

    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


    • Editado kaneda182 terça-feira, 14 de agosto de 2012 18:58
    terça-feira, 14 de agosto de 2012 18:57

Respostas

  • 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

    quinta-feira, 16 de agosto de 2012 21:42
    Moderador

Todas as Respostas

  • 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

    terça-feira, 14 de agosto de 2012 20:14
  • 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

    terça-feira, 14 de agosto de 2012 20:40
  • 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

    quinta-feira, 16 de agosto de 2012 21:42
    Moderador