1.Adicione um botão em uma planilha chamada VBScript, colocando nesta aba os parâmetros a serem utilizados na consulta ao EPM, conforme a figura a seguir.

Criação do botão

A adição de um botão na planilha pode ser realizada através do menu de desenvolvimento do Excel, com o modo Design habilitado.

2.A segunda etapa é escrever um procedimento que lê os parâmetros da planilha e executa uma consulta ao EPM Server ao clicar em um botão. O procedimento é o seguinte:

Sub GetEpmTagValues()
  Dim EpmServer, BVName
  Sheets("VBScript").Select
  EpmServer = Range("B2")
  BVName = Range("B7")
  MsgBox "Function EpmAggregate" & vbNewLine _
    & "Server: " & EpmServer & vbNewLine _
    & "BV-Name: " & BVName
  Range("A10:C16").Select
  Selection.ClearContents
  Selection.FormulaArray = "=EpmAggregate(VBScript!B2,_
    VBScript!B4, VBScript!B5, VBScript!B3,_
    VBScript!B6, , VBScript!B7)"
End Sub

Este procedimento seleciona a planilha VBScript, lendo os valores das células B2 e B7 e associando às variáveis EpmServer e BVName, que são apresentadas em uma janela de mensagem antes de efetivamente executar a consulta. Na sequência, é selecionada uma área, células A10:C16, onde é colocada a função no formato Multi-Array Formula com a consulta.

3.Após associar o comando de pressionamento do botão com o procedimento GetEpmTagValues, desabilite o modo Design e clique no botão para mostrar a janela com a mensagem informando o servidor e a variável a ser consultada, conforme a figura a seguir.

Janela de mensagem

4.Ao clicar em OK nesta janela de mensagem, o código que copia a função no formato Multi-Array Formula para as células selecionadas é executado, apresentando os resultados segundo o Layout definido como padrão.

Resultado do procedimento

Note que a fórmula foi definida como sendo do tipo Multi-Array Formula, uma vez que é apresentada entre chaves. É importante ressaltar que as fórmulas a serem colocadas nas células através de procedimentos escritos em VBA devem estar neste formato, ou seja, utilizando o método de atribuição de conteúdo à célula FormulaArray.

Uma outra forma de utilizar as funções do EPM em procedimentos escritos em VBA é através do carregamento dos objetos de consulta do EPM. Desta forma, é possível processar os dados no próprio script e posteriormente dispor os resultados na planilha no formato desejado. O exemplo a seguir demonstra esta outra forma.

1.Adicione um botão em uma planilha, conforme a figura a seguir.

Adicionar botão na planilha

2.Escreva um procedimento que executa uma consulta ao EPM Server ao clicar em um botão. O procedimento é o seguinte:

Sub Execute_Dataset01()
  Dim o As Object
  Static queryResultAsObject As Variant
  Dim callerRows As Integer
  Dim callerColumns As Integer
  Set o = CreateObject("AutomationAddIn.EpmFunctions")
  With o
    queryResultAsObject = .EpmQuery("", "Dataset01")
  End With
  callerRows = UBound(queryResultAsObject, 1)
  callerColumns = UBound(queryResultAsObject, 2)
  Dim startRange As Range
  Set startRange = Range("A4")
  Dim endRow As Integer
  endRow = startRange.Row + callerRows
  Dim endColumn As Integer
  endColumn = startRange.Column + callerColumns
  Dim endRange As Range
  Set endRange = Cells(endRow, endColumn)
  Dim targetRange As Range
  Set targetRange = Range(startRange, endRange)
  With targetRange
    .Value = queryResultAsObject
  End With
End Sub

Este procedimento cria um objeto de consulta, AutomationAddIn.EpmFunctions, e executa o método EpmQuery deste objeto, deixando vazio o campo da conexão, ou seja, utiliza a conexão padrão, e informando o nome de um Dataset previamente criado no EPM Studio, Dataset01. Uma vez executado este método, os resultados são dispostos na planilha a partir da célula A4, definida como ponto inicial no procedimento.

3.Após associado o comando de pressionamento do botão com o procedimento Execute_Dataset01, desabilite o modo Design e clique no botão para realizar a consulta e apresentar os resultados, conforme a figura a seguir.

Resultado do procedimento

Esta outra forma de execução de funções do EPM em scripts VBA do Excel dá total autonomia e flexibilidade para o processamento dos dados via script, assim como a apresentação dos resultados na planilha.