Bugs e soluções no excel 2003 avançado

Question

O Objetivo deste tópico não é perguntar mas apontar problemas e soluções para quem já passou por uma situação difícil e sabe que outros usuários de excel avançado podem passar também. Vou iniciar aqui apontando dois problemas encontrados e as soluções que adotei para contornar o bug do produto. Espero assim contribuir com outros usuários que também podem passar por dificuldades semelhantes quando testamos os produtos microsoft na sua capacidade máxima. Usuários com nível de conhecimento inferior não devem postar tópicos aqui, pois o objetivo é agrupar aqui erros mais complexos e cavernosos que podemos encontrar e não os mais básicos.

1º - Tabela Dinâmica não é atualizada corretamente com acesso a banco externo (ODBC):

Uma ferramenta que desenvolvi em excel com a utilização de macros utilizava uma tabela dinâmica com acesso ODBC em um banco de dados Access. A finalidade era de permitir para o usuário sincronizar o relatório automaticamente com os dados do banco. O problema foi detectado quando durante a alteração de alguns dados no banco e após pela macro executar o comando de atualização dos dados da tabela dinâmica os dados mais antigos continuavam a aparecer na planilha. Tudo era atualizado corretamente, as áreas Página, Coluna e Dados da tabela dinâmica menos a área Linha. Os campos da área linha permitem realizar filtros pelo usuário (Onde aparece a opção ( )Mostra Tudo e item a item) . Exemplo: A primeira coluna da tabela era "Tipos de transporte" e no banco existia o dado Caros. O usuário atualiza a dinâmica e aparece na seleção o filtro para Caros, só que alguém posteriormente corrigiu no banco de dados a palavra "Caros" para "Carros" e quando a macro atualiza o excel aparecem os dois "Caros" e "Carros" só que a consulta do banco retorna apenas o item "Carros". Isto só ocorreu na área de linhas da tabela dinâmica e se arrasto o campo da área de linhas para página ele informa corretamente apenas a opção "Carros", mas se volto a colocar o mesmo campo na área de linhas aparece a opção "Carros" e "Caros"

Dica importante: A consulta externa no banco para fonte de origem de dados da tabela dinâmica cria um cache interno na planilha excel, sendo assim a planilha embora não tenha dados visíveis ganha um peso considerável (no meu caso 60 Mb) e a vantagem é que o usuário poderá levar esta planilha para outros micros que não tenham acesso ao banco de daos e realizar as mudanças na tabela dinâmica como se estivesse acessando o banco. Lembrando que somente não poderá realizar a atualização dos dados visto que a conexão com o banco não existe. O problema identificado demonstra que o cache não é totalmente zerado quando solicitamos a atualização dos dados com um novo retorno de dados do banco.

Para corrigir o problema:

Solução Parte 1:

---

Preparar a tabela dinâmica origem:

1º Precisamos limpar o conteúdo da dinâmica para não precisarmos iniciar uma nova do zero, a que eu tinha estava com cerca de 60 campos calculdados então imagina o trabalho que seria iniciar uma do zero. Para isso é necessário primeiro garantirmos que a consulta que retorna do access não traga informações, neste caso altere o banco ou parâmetros da consulta no access de forma que venha nenhuma ou apenas 1 linha com dados que vc. sabe que não serão facilmente alterados para garantirmos um mínimo de visualização das informações na tabela. O trabalho anterior foi realizado no banco de dados e não na tabela dinâmica.

2º Agora na dinâmica é necessário que o flag nas opções da tabela chamado "Preservar formatação" não esteja marcado ou não vai funcionar, altere o flag se necessário e volte para a tabela dinâmica.

3º Arraste todos os campos da área de linhas para fora de forma a excluir todos os campos armazenados nesta área.

4º Realize a atualização da tabela clicando na opção "Atualizar Dados" do botão esquerdo do mouse (direito se vc. for canhoto).

5º Após ter atualizado os dados volte a recolocar os campos na posição correta da área linha da tabela.

6º Se utilizava antes a opção "Preservar formatação" nas opções da tabela, volte a marcar este item. A função desta opção é a demanter a FORMATAÇÃO (Fonte, cor, tamanho etc.) jamais poderia guardar dados não existentes (BUG).

7º Salve a planilha com a tabela dinâmica neste estado.

8º Não esquecer de acertar o banco de dados para que a consulta volte a trazer as informações corretamente.

Importante: Não atualize mais esta dinâmica para não carregar novamente o cache dela. A utilização da consulta direta no banco foi por 2 motivos, 1) Mais de 65.000 linhas de retorno excede a capacidade do excel e 2) Atualização direta dos dados com o que está no banco sem manipulações intermediárias.

Pronto acertamos a tabela dinâmica que está com o cache praticamente vazio.

Solução Parte 2:

---

Vamos criar o mecanismo de atualização para o usuário final:

1º A pasta que contém a tabela dinâmica "vazia" deve ficar oculta e na mesma planilha deveremos ter uma outra pasta com um nome parecido como "Gerar Relatório"

2º Crie um botão com caption semelhante a "Gerar Relatório"

3º Esta macro deverá fazer o seguinte quando vc. clicar nele:

Tornar a planilha da tabela dinâmica visível, se estiver oculta (plan2.visible = true);

Copiar a planilha para um novo workbook (plan2.copy)

Ocultar a planilha anterior para que o usuário não possa manipular (plan2.visible = false)

Atualizar a tabela dinâmica da planilha copiada (ActiveSheet.PivotTables("Tabela dinâmica1").PivotCache.Refresh)

Pronto o usuário terá sempre a dinâmica com dados corretos, apenas devemos orientar a sempre utilizar a ferramenta clicando no botão "Gerar Relatório" e  não tentar atualizar mais a atualização direta na dinâmica.

Answer 1

**2º Quando tento ativar a visibilidade de um item da tabela dinâmica via macro aparece um erro:**Problema:

O seguinte código começou a gerar erro sem uma causa específica:

For X = 2 To ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("NOME DO CAMPO").PivotItems().Count

   if ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("NOME DO CAMPO").PivotItems(X).Visible = false then

       ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("NOME DO CAMPO").PivotItems(X).Visible = True

   endif

Next

Embora a sintaxe esteja correta e estivesse funcionando num determinado momento começou a gerar o seguinte erro:

Erro em tempo de execução 1004:

Não é possível definir a propriedade Visible da classe PivotItem

Mesmo que você grave uma macro que o próprio excel cria esta linha de comandos ele mesmo não consegue executar a macro que ele mesmo gravou em seguida.

Motivo:

A opção de campo em "Configurações de Campo" -> "Avançado" -> "Opções de Auto Classificação" quando setada em "Crescente" ou "Decrescente" não deixa que o comando ".visible = true" funcione. O interessante é que visible = false funciona. Se o usuário ativar manualmente na tabela dinâmica funciona, mas o comando não.

Solução:

Inserir 2 linhas no código para desativar a autoclassificação e depois do processamento voltar a ativar a auto classificação

ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("NOME DO CAMPO").AutoSort xlManual, "NOME DO CAMPO"

For X = 2 To ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("NOME DO CAMPO").PivotItems().Count

   if ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("NOME DO CAMPO").PivotItems(X).Visible = false then

       ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("NOME DO CAMPO").PivotItems(X).Visible = True

   endif

Next

ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("NOME DO CAMPO").AutoSort xlAscending, "NOME DO CAMPO"

Answer 2

Boa tarde,

Verifiquei que esta ação não soluciona o código no Excel 2010.

o comando:

pf.PivotItems(i).Visible = False

(Me parece que ela só faz isso com data, pois consegue eliminar o "(blank)" ("Vazias") no Filtro de datas)

Resulta em:

Erro em tempo de execução 1004:

Não é possível definir a propriedade Visible da classe PivotItem

Segue o código Completo:

Sub FilterPivotDates()

Dim dStart As String

Dim pt As PivotTable

Dim pf As PivotField

Dim pi As PivotItem

Dim i As Integer

Application.ScreenUpdating = False

'On Error Resume Next

dStart = Range("StartDate").Value

dStart = (Right(dStart, 4) & Left(Right(dStart, 7), 2) & Left(dStart, 2)) + 0

Set pt = ActiveSheet.PivotTables(1)

Set pf = pt.PivotFields("Data_Liberacao_Req")

pt.ManualUpdate = True

pf.EnableMultiplePageItems = True

pt.ClearAllFilters

pt.PivotCache.Refresh

pf.AutoSort xlManual, "Data_Liberacao_Req"

For i = 1 To pf.PivotItems.Count

If (Right(pf.PivotItems(i), 4) & Left(Right(pf.PivotItems(i), 7), 2) & Left(pf.PivotItems(i), 2)) + 0 > dStart Then

pf.PivotItems(i).Visible = False

End If

Next i

pf.AutoSort xlAscending, "Data_Liberacao_Req"

Application.ScreenUpdating = True

pt.ManualUpdate = False