Guru do Excel e Power BI

Fórum de Dúvidas sobre Excel e Power BI
https://gurudoexcel.com/

Integração Access+Excel (Exibir tabelas do access filtradas)

https://gurudoexcel.com/viewtopic.php?t=113

Página 1 de 1

Integração Access+Excel (Exibir tabelas do access filtradas)

Enviado: 30 Abr 2015 às 16:56
por k4n3d4
Boa tarde amigos,

Preciso criar tabelas e gráficos no excel usando um banco de dados do access sendo que esse banco de dados nunca será alterado.

Sei que eu poderia simplesmente poderia puxar a tabela usando o menu "dados>do access" mas como a tabela é muito grande, gostaria que apenas algumas colunas fossem usadas nessa busca. Alguém aí poderia me ajudar?

Obrigado

Re: Integração Access+Excel (Exibir tabelas do access filtra

Enviado: 30 Abr 2015 às 18:18
por FelipeGualberto
No assistente de importação de dados, clique em Propriedades:
Imagem

Em seguida, clique na aba Definição, selecione SQL para Tipo de comando e escreva uma instrução SQL que restrinja a quantidade de dados a importar, como o exemplo mostrado a seguir:
Imagem

Re: Integração Access+Excel (Exibir tabelas do access filtra

Enviado: 03 Mai 2015 às 14:40
por k4n3d4
Muito obrigado pela ajuda ^_^

Entendi perfeitamente porem não estou conseguindo fazer referencia a uma célula da tabela, por exemplo, nesta imagem anexa eu gostaria de substituir o "BANDAI" da fórmula pelo conteúdo de E1 que tem escrito a palavra "BANDAI". Desde já agradeço a ajuda.

Imagem

Integração Access+Excel (Exibir tabelas do access filtradas)

Enviado: 04 Mai 2015 às 11:33
por FelipeGualberto
Não é possível referenciar diretamente uma expressão da consulta a uma célula. Você terá que usar VBA para fazer essa atualização.
Minha sugestão é você gravar uma macro alterando a instrução manualmente, editar o código gerado e substituir a parte desejada pela referência à célula.
Só não se esqueça que critérios de consulta de texto são aspas simples, e não duplas.

Re: Integração Access+Excel (Exibir tabelas do access filtra

Enviado: 04 Mai 2015 às 15:36
por k4n3d4
Bem, obrigado pelo esclarecimento primeiramente.

Tentei fazer o procedimento mas quando eu criei a macro já na importação dos dados, a mesma não executou, dando erro de tempo de execução.

Em seguida tentei fazer uma macro apenas da modificação deste argumento e também não funcionou pois estava dizendo que "o intervalo de dados não estava sendo atualizado".

Será que minha macro está vindo muito suja ou algo do tipo? Obrigado pela ajuda

Integração Access+Excel (Exibir tabelas do access filtradas)

Enviado: 04 Mai 2015 às 16:30
por laennder
Coloque a sua macro, que fica mais fácil analisar o que pode estar acontecendo.

Re: Integração Access+Excel (Exibir tabelas do access filtra

Enviado: 04 Mai 2015 às 16:42
por k4n3d4
Realmente, acho que postando o código fica melhor
Aqui está:
Código: Selecionar todos
Sub Macro1()
'
' Macro1 Macro
'
' Atalho do teclado: Ctrl+q
'
    Dim VALOR As Integer
    VALOR = Range("f1").Value
    
    Range("Tabela_bd_teste3.accdb[#All]").Select
    With ActiveWorkbook.Connections("bd_teste3").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array( _
        "SELECT [PERSONAGEM], [LOCAL DO ESTOQUE], [MODELO], [VERSÃO] FROM [tb_teste] WHERE [MODELO] = VALOR" _
        )
        .CommandType = xlCmdSql
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\lmendes\Desktop\bd_teste3.accdb;Mode=Share Deny Write;Exten" _
        , _
        "ded Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mod" _
        , _
        "e=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create" _
        , _
        " System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without R" _
        , "eplica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
        )
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = "C:\Users\lmendes\Desktop\bd_teste3.accdb"
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
        ''.ServerFillColor = False
        ''.ServerFontStyle = False
        ''.ServerNumberFormat = False
        ''.ServerTextColor = False
    End With
    With ActiveWorkbook.Connections("bd_teste3")
        .Name = "bd_teste3"
        .Description = ""
    End With
    ActiveWorkbook.Connections("bd_teste3").Refresh
    With Selection.ListObject.QueryTable
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
    End With
End Sub

Integração Access+Excel (Exibir tabelas do access filtradas)

Enviado: 04 Mai 2015 às 16:49
por laennder
tente com essa linha assim:
Código: Selecionar todos
"SELECT [PERSONAGEM], [LOCAL DO ESTOQUE], [MODELO], [VERSÃO] FROM [tb_teste] WHERE [MODELO] = '" & VALOR & "'"

Integração Access+Excel (Exibir tabelas do access filtradas)

Enviado: 04 Mai 2015 às 17:03
por k4n3d4
Fiz isso Laennder, desta vez acusou que os "dados estão sendo atualizados em segundo plano" Obrigado

Integração Access+Excel (Exibir tabelas do access filtradas)

Enviado: 04 Mai 2015 às 17:18
por laennder
mas fica travado nessa msg?

Re: Integração Access+Excel (Exibir tabelas do access filtra

Enviado: 04 Mai 2015 às 17:23
por k4n3d4
Sim, estava travando nesta msg. apaguei algumas coisas no final do codigo e a macro terminou funcionando normalmente(pelo menos até agora, não posso continuar os testes agora mas amanhã estou de volta).

até então o codigo está assim:
Código: Selecionar todos
Sub Macro1()
'
' Macro1 Macro
'
' Atalho do teclado: Ctrl+q
'
    Dim VALOR As Integer
    VALOR = Range("f5").Value
    
    Range("Tabela_bd_teste3.accdb[#All]").Select
    With ActiveWorkbook.Connections("bd_teste3").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array( _
        "SELECT [PERSONAGEM], [LOCAL DO ESTOQUE], [MODELO], [VERSÃO] FROM [tb_teste] WHERE [MODELO] = '" & VALOR & "'" _
        )
        .CommandType = xlCmdSql
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\lmendes\Desktop\bd_teste3.accdb;Mode=Share Deny Write;Exten" _
        , _
        "ded Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mod" _
        , _
        "e=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create" _
        , _
        " System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without R" _
        , "eplica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False" _
        )
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = "C:\Users\lmendes\Desktop\bd_teste3.accdb"
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
        ''.ServerFillColor = False
        ''.ServerFontStyle = False
        ''.ServerNumberFormat = False
        ''.ServerTextColor = False
    End With
    With ActiveWorkbook.Connections("bd_teste3")
        .Name = "bd_teste3"
        .Description = ""
    End With
    ActiveWorkbook.Connections("bd_teste3").Refresh
    
    End Sub
Mais uma vez obrigado pela força! amanhã vou realizar uns testes finais para vir aqui fechar o topico como resolvido.
Todos os horários são UTC-03:00
Página 1 de 1
Powered by phpBB® Forum Software © phpBB Limited