Excel Gerar gráfico em Userfom Excel com banco de dados em access

[josequali]

Bom dia! Como gerar um gráfico no userform do excel com banco de dados no access.

 

Condição: Os gráficos só podem carregar se o banco de dados estiver correto, se não, não carrega mas também não pode dar erro (o caminho eu puxo da célula a1 da minha planilha).

 

No meu modelo eu preciso que gere o primeiro gráfico com as cores dos coelho e o segundo gráfico com o estado de saúde do coelho.

 

OBS: Eu só não faço direto conectando ao banco de dados pelo modo habitual indo em Dados e obter dados do access para gerar os gráficos porque a planilha no projeto ficará invisível, então não poderei mostrar o caminho por este método, por isso preciso fazer assim.  rs

 

Segue em anexo o modelo da planilha e o banco de dados. Desde já agradeço a ajuda de todos  

Projeto teste.rar

[josequali]

@Basole @Midori @AfonsoMira Estou adicionado alguma explicações aqui para ver se fica mais fácil me ajudar  rs, caso possam  

 

Basicamente é assim, preciso gera um gráfico na planilha excel a partir de um banco de dados no access, sendo que esse gráfico ao ser gerado deve ficar sempre com o mesmo nome.

 

Atualmente eu faço assim utilizando uma macro.

 

Método por macro.

Criando tabela:

1) Inserir
2) Tabela dinâmica
3) usar fonte de dados externa
4) procurar mais
5) Disco C
6) Banco de dados e seleciono o arquivo Acess
7) Seleciono Tabela1
7) Seleciono ok

Configurando Tabela dinâmica

1) Em rotulo de linha coloco empresa e em Valores coloco motivo
2) Seleciono o tipo de gráfico de barras 
3) Removo linha de grande, mudo a fonte para 10 Segoe UI, removo as bordas e adiciono rótulo.

Fechando a planilha

Ao fechar a planilha deve salvar.

Abrindo novamente a planilha

 

Deve atualizar a tabela dinâmica (esse comando já deixei no evento open).

 

 

Como eu desejaria que acontece-se:

Que ela pega-se o caminho do banco de dados access na célula A1 e fize-se todo o processo, ao invés de fazer as etapas acima, visto que posso mudar meu banco de dados de pasta.

 

Segue em anexo o modelo de planilha e banco de dados. Para a macro funcionar você deve colar a pasta Bancodedados  e Planilha dentro do Disco C, fora da pasta Teste.

 

Espero que tenha entendido, não sei se minha explicação ajudou muito, rs. Desde já muito obrigado!  

 

 

 

 

teste.rar

[Midori]

@josequali Sua conexão já está criada na planilha como BancodeDados10, tente mudar o caminho em SourceConnectionFile,

 

ThisWorkbook.Connections("BancodeDados10") _
    .OLEDBConnection.SourceConnectionFile = [A1]
    
ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
    ThisWorkbook.Connections("BancodeDados10"), Version:=xlPivotTableVersion12) _
    .CreatePivotTable TableDestination:="Plan1!R17C5", TableName:= _
    "Tabela dinâmica2", DefaultVersion:=xlPivotTableVersion12

 

[josequali]

@Midori Muito obrigado! Desculpe pela demora do retorno . Deus te abençoe rs

[josequali]

@Midori vamos supor o seguinte cenário:

 

Tenho um banco de dados em acess em uma pasta (Pasta A) e uma planilha com uma tabela dinâmica conectada ao access em outra pasta (Pasta B).

 

Agora ocorre a seguinte situação:

 

Transferir o banco de dados access da pasta A para a Pasta C. Quando faço isso automáticamente a tabela dinâmica perde o acesso e solicita que eu mostre onde estar o banco de dados quando eu vou atualizar ela.

 

Me dúvida é: como adicionar o caminho do banco de dados em uma célula para a tabela dinâmica se atualize a partir do caminho fornecido por exemplo na célula A1.

 

Para ver essas ações pode utilizar o modelo de planilha que já está anexado aqui nesse post. Desde já agradeço pela ajuda e disponibilidade. Obrigado

[Midori]

Em 04/06/2021 às 19:03, josequali disse:

Me dúvida é: como adicionar o caminho do banco de dados em uma célula para a tabela dinâmica se atualize a partir do caminho fornecido por exemplo na célula A1.

O código que postei deve resolver isso já que o caminho do banco de dados é atualizado com base nessa célula.

[josequali]

@Midori Então o código que uso é esse para atualizar a tabela dinamica.

 

Dim pivotTable As pivotTable

  For Each PLAN In ActiveWorkbook.Sheets
    For Each pivotTable In PLAN.PivotTables
        pivotTable.RefreshTable
    Next
  Next

 o que você me repassou foi esse

ThisWorkbook.Connections("BancodeDados10") _
    .OLEDBConnection.SourceConnectionFile = [A1]
    
ThisWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
    ThisWorkbook.Connections("BancodeDados10"), Version:=xlPivotTableVersion12) _
    .CreatePivotTable TableDestination:="Plan1!R17C5", TableName:= _
    "Tabela dinâmica2", DefaultVersion:=xlPivotTableVersion12

 

mas ele cria uma nova tabela dinâmica a partir do banco de dados, no entanto, eu não quero que crie um nova tabela eu os quero que atualize a que eu já tenho que é a tabela dinâmica 1 e 2.  

[Midori]

@josequali Tentei fazer isso atribuindo o SourceConnectionFile e depois chamando os métodos para reconectar e aplicar o refresh, mas não deu certo. A forma que encontrei foi atribuindo novamente a string de conexão com o novo data source. Talvez não seja a melhor forma, mas aqui deu certo.

 

Sub Atualiza()
    Dim Conexao As String
    
    With ThisWorkbook.Connections("BancodeDados10")
        Conexao = .OLEDBConnection.Connection
        Conexao = Replace(Conexao, Split(Split( _
            .OLEDBConnection.Connection, ";")(3), "=")(1), [A1])
        .OLEDBConnection.Connection = Conexao
        ActiveSheet.PivotTables("Tabela dinâmica2").PivotCache.Refresh
    End With
End Sub