[Resolvido]Consulta X Planilha Excel

Grandes Mestres,

Não sei se é exatamente aqui que deveria postar meu problema (mistura de tabelas e consultas Access com Planilhas Excel) mas vou pedir a opinão dos mestres. É o seguinte:
Tenho um grande banco de dados com dezenas de tabelas. Fazendo cruzamentos dessas tabelas (usando consultas) obtenho um valor final e uma relação de registros que foram filtrados sob determinadas condições. Essas relações e valores finais chegam +- 250. Meu chefe pediu para colocar todos esses valores finais em uma planilha Excel e ao clicar no valor exibir (em outra planilha) a relação referente aquele valor final. Nesta planilha principal tenho exatamente 276 valores e consequentente 276 outras planilhas (como disse anteriormente, ao clicar no valor abre outra planilha com relação detalhada dos dados).
O banco de dados é atualizado diariamente e essas planilhas auxiliares e valores finais ficam desatualizadas. Sei que entrando em cada uma das planilhas tenho como atualizar os dados. Só que é uma a uma e só eu posso atualizar (o chefe não sabe e nem tem o banco de dados para colocar para atualizar automaticamente). Queria ver se tem um modo (código ou macro) para atualizar todas essas planilhas clicando apenas em um botão.
As planilhas forma criadas em DADOS -> OBTER DADOS EXTERNOS -> ACCESS.
Alguém me dar uma luz?

Muito grato a todos

No momento que se faz esse procedimento DADOS -> OBTER DADOS EXTERNOS -> ACCESS, costuma ter opções de atualização automática de tempos em tempos, como por exemplo de segundo a segundo. Não resolve?

Grande Criquio,

Não grande mestre. Se assim o fizer quando o chefe estiver usando a planilha principal ou qualquer uma outra e for atualizar dará uma mensagens de advertência por não encontrar o caminho do banco de dados. O chefe só tem a planilha principal e as secundárias. Por isso que tenho sempre que deixar as planilhas sempre atualizadas e queria um modo de fazer isso mais prático. Uma macro, um código, algo que evitasse eu de abrir planilha por planilha para atualiza. Vale lembrar que todas estas planilhas estão na pasta do DropBox. Atualizo aqui e aparece atualizadas na máquina do chefe.
Fico no aguardo.

Muito grato

Deixa ver se entendi. Você tem umas dezenas de planilhas no dropbox, sendo essas as planilhas as quais seu chefe tem acesso, e você quer atualizar todas elas com um clique, é isso mesmo? Se for, uma forma de tentar fazer isso, pode ser colocando uma função em um botão de um formulário no Access ou criando uma função em um módulo global que pode ser chamada através de um atalho de teclado definido em uma macro AutoKeys. Tente algo assim:

Código:

Dim dExcel As Object,  fs As Object, f As Object, f1 As Object, fc As Object
 
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(CurrentProject.Path & "\")
    Set fc = f.Files
    Set dExcel = CreateObject("Excel.Application")

        dExcel.Visible = False
   
    For Each f1 In fc
        If Right(f1.Name, 3) = "xls" Then
            dExcel.workbooks.Open CurrentProject.Path & "\" & f1.Name
            dExcel.ActiveWorkbook.RefreshAll
            dExcel.Application.Quit
        End If
    Next

    Set dExcel = Nothing
    Set fc = Nothing
    Set f = Nothing
    Set fs = Nothing

O exemplo acima é para arquivos xls. Caso seja xlsx, mude a linha:

Código:

        If Right(f1.Name, 3) = "xls" Then

para:

Código:

        If Right(f1.Name, 4) = "xlsx" Then

Lembrando tambem que estou supondo que tanto o banco Access quanto as planilhas estão na mesma pasta.

Caro Mestre,

O banco não é na mesma pasta mas adaptei o código e executei:
1. Não está atualizando
2. Abre a planilha e pede para salvar sem fazer nada
3. Dá uma mensagem: Esta ação cancelará um comando "Atualizar dados" pendente. Continuar?
4. Não salva as planilhas automaticamnte, tendo que confirmar.

Queria também limitar estas atualizações as palnilhas de nomes começados por: "Plan MM_2012...". Tem na mesma pasta dezenas de planilhas que são de outros serviços e não tem nada para atualizar.

Muito grato

Grande abraço

Caro Criquio,

Fiquei fazendo umas alterações, adaptações, estudando seu código e consegui colocá-lo para funcionar. O que falta, que ainda não consegui, é salvar cada planilha automaticamente. Sei que tem haver com 'ActiveWorkbook.Save', mas não consegui colocar no local correto.
Só falta isso.

Muito grato

Grande Abraço

Grande Mestre,

Já está quase tudo OK. Só falta limitar para atualizar apenas as planilhas que começam com Plan MM_2012. Como faço?

Muito Grato

GRANDE ABRAÇO

Tente alterar a linha abaixo:

Código:

        If Right(f1.Name, 3) = "xls" Then

para:

Código:

        If Right(f1.Name, 3) = "xls" And Left(f1.Name, 12) = "Plan MM_2012" Then

Conseguiu a parte de gravar tambem? É com a linha abaixo, caso não tenha conseguido:

Código:

        dExcel.ActiveWorkbook.Save

Depois poste a forma completa como ficou para que outros possam usufruir da solução.

Grande Criquio,

Estudando a situação do IF para 'xlsx' consegui resolver o problema que você acaba de confirmar o modo de se fazer. Muito grato. Foi muito bom o bizú inicial que forçou a pesquisar e aprender. Muito grato.
Queria agora que fosse exibindo o nome da planilha que está sendo atualizada no momento. Com MSGBOX faço, mas tem que dar ENTER (confirmar). Como faço?

Muito grato

Coloque um label e utilize a linha abaixo no lugar da MsgBox:

Me.NomeDoLabel.Caption = "Texto da MsgBox"

Grande,

Não consegui entender o queres dizer.

Você precisará colocar um controle para exibir o nome da planilha, seja um rótulo ou um campo texto. Ou então colocar para ser exibido na barra de título do formulário, caso ele não esteja sem borda. O primeiro caso é conforme passei no post anterior. Basta colocar a linha que passei no lugar da linha aonde você colocou a MsgBox. No segundo caso, basta colocar a mesma linha no mesmo local mas mudando Me.NomeDoLabel.Caption para Me.Caption.

Meu Mestre,

Entendido e tudo OK. GRANDE MESTRE. Tudo funcionando perfeitamente.

Muito obrigado por mais essa.

Grande abraço

Grato. Poderia por gentileza colocar aqui a função completa para que outros membros possam usufruir da mesma?

Aí está o código funcionando perfeitamente em um botão de um formulário Access em que atualiza 31 planilhas contendo de 15 a 20 plan dentro de cada uma. Os registros são oriundos de um banco de dados Access, onde são feitos centenas de filtros ou cruzamentos de informações para gerar no final uma planilha principal contendo apenas os totais resultantes destas consultas e clicando nesses valores são exibidos os registros referentes aquele número final. Na medina que vai atualizando cada planilha o seu nome vai sendo exibido no formulário Access.

Ficou muito bom graças ao grande Mestre Criquio.

Private Sub Comando186_Click()
Dim dExcel As Object, fs As Object, f As Object, f1 As Object, fc As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(CurrentProject.Path & "\")
Set fc = f.Files
Set dExcel = CreateObject("Excel.Application")
dExcel.Visible = False
For Each f1 In fc
If Right(f1.Name, 4) = "xlsx" And Left(f1.Name, 12) = "Plan MM_2012" Then
dExcel.workbooks.Open CurrentProject.Path & "\" & f1.Name
dExcel.ActiveWorkbook.RefreshAll
dExcel.ActiveWorkbook.Save
Me.PlanAtualizando.Caption = f1.Name
dExcel.Application.Quit
End If
Next
MsgBox "Atualização concluída com sucesso... Tecle ENTER!", vbCritical, "ATUALIZAÇÃO"
Set dExcel = Nothing
Set fc = Nothing
Set f = Nothing
Set fs = Nothing


End Sub

Grande abraço a todos

Grato pelo retorno. Só faltou clicar no Resolvido então, não? Eu mesmo clico.