Atualizar excel pelo Access

Boas,

Estou com dificuldade de atualizar uma planilha em excel que tem vinculo com uma consulta no access.

No projeto tenho um gráfico que é atualizado por dados do excel, onde o excel é atualizado com dados do access, o excel faz os cálculos e retorna os valores à uma consulta no access que gera o gráfico.

Está funcionado quase perfeito, mas tenho que abrir a planilha atualizar os dados com o access. Depois disso o access já atualiza a consulta que gera o gráfico.

A minha intenção é clicar num botão no formulário que tenho o gráfico e ele faça a atualização sozinho. (Abra o excel, atualize os dados no excel com base no access e atualize a consulta).

Se possível o excel estar anexado dentro do BD para que não tenha problema com pastas na rede, alteração do nome do excel, etc.

Conto com a ajuda de vocês.

Cumprimentos,

Bruno Nunes

Bom dia!

Na tentativa de solução encontrei uma forma de atualizar, mas não fecha o excel em segundo plano e gera erro no banco.

Segue o código abaixo.

Código:

Private Sub btn_refresh_Click()

Dim AppExcel As Object
Dim excelwb As Excel.Workbook

Set AppExcel = CreateObject("Excel.Application")

Set excelwb = AppExcel.Workbooks.Open(CurrentProject.path & "\mapadeferias.xlsx")

excelwb.RefreshAll
excelwb.close
DoCmd.SetWarnings False
excelwb.Close
DoCmd.SetWarnings True
Set excelwb = Nothing
Set AppExcel = Nothing

End Sub

Cumprimentos,

Bruno Nunes

Olá Bruno Nunes,

Tente assim:

Código:

Private Sub btn_refresh_Click()
    Dim AppExcel As Object
    Dim excelwb As Excel.Workbook
   
    Set AppExcel = CreateObject("Excel.Application")
   
    Set excelwb = AppExcel.Workbooks.Open(CurrentProject.path & "\mapadeferias.xlsx")
   
    excelwb.RefreshAll
    excelwb.Close
    DoCmd.SetWarnings False
    excelwb.Close
    AppExcel.Quit 'fechar excel
    DoCmd.SetWarnings True
    Set excelwb = Nothing
    Set AppExcel = Nothing
End Sub

Abraço

Tentei código e gerou erro de acesso ao BD, diz que não está conectado. E Depois outro erro que diz que o excel não pode ser atualizado.

Olá Bruno Nunes,

Será melhor fazer assim, monte uma base de dados com dados de amostragem e o formulario com o código que postou para se testar.

Assim fica mais fácil para qualquer membro poder testar e ajudar.

Abraço

AhTeixeira,

Conforme solicitado, segue o BD.

O botão "Refresh" é no Form "Planeamento". Ele abre o arquivo mapaferias.xlsx que esta em anexo e atualiza as tabelas que por sua vez atualiza as consultas no access. Após as consultas atualizadas os gráficos na aba "BSC - Balance scorecard" são atualizados.

O Problema é que o excel não fecha e fica com gerando erro no BD. Já tentei "workbooks.close" e outras funções que gerou o mesmo problema.

Cumprimentos,

Bruno Nunes

Olá Bruno Nunes,

Apaguei o seu anexo, faltava as base de dados.


Do projeto que preparou elimine as tabelas ligadas (vinculadas) e importe do BakEnd.
Depois ajuste os dados para dados de amostragem e partilhe de novo.

Abraço

AHTeixeira,

Obrigado pela lembrança, havia esquecido que já tinha separado o BD.

Já editei o arquivo e coloquei o novo na mensagem.

Grato,

Bruno Nunes

Tentando pensar de outra forma para criar os gráficos de forma mais eficiente.

Se eu gerar os gráficos e atualizar tudo no excel e depois só copiar os gráficos como imagem para o formulário, será que ficará mais eficiente?

Cumprimentos,

Bruno Nunes

Olá,

Estive analisar, efetivamente pendura e não compreendi bem o que está a fazer no Excel.
No entanto se são gráficos e cálculos, acho que deveria fazer tudo no Access.
Eu pessoalmente sou adepto dessa forma, quando uso Excel é para exportação de dados para fornecer a terceiros.

Abraço

No Excel tenho um mapa de férias, onde vai gerar minha disponibilidade de horas de mão de obra por dia. Com isso gero um gráfico de Disponibilidade total.

Depois importo os trabalhos que tenho na fábrica e os previstos, faço o cálculo de quantas horas por dia são necessárias para cada trabalho e depois faço a soma do total de horas por dia que tenho de trabalho.

Após isso gero um gráfico "Disponibilidade total x Horas de trabalho".

Motivo destes gráficos ver se tenho mais horas de trabalho do que tenho de mão de obra, ou se tenho ociosidade na fábrica e preciso de mais trabalhos.

Como tenho o domínio no Excel, achei mais fácil ter esses cálculos mais trabalhados no Excel. Porque no Access não consigo fazer esses cálculos.


Estou quase finalizando a solução, como:

1. Anexei o ficheiro Excel numa tabela
2. Abro o ficheiro em uma pasta temp.
3. Exporto os dados do Access para o Excel
4. Importo os dados para uma tabela e faço uma consulta filtrando somente os dias que preciso. Ou gero os gráficos no Excel e faço uma cópia para o formulário.
5. Se importar os dados e gerar uma consulta, faço um requery dos dados do gráfico por um botão.


Mas como quero que fique "On-Time", acho que vou optar em importar os dados e gerar os gráficos pelo próprio Access.

Acho que consegui explicar o motivo.

Consegue ajudar neste caso?

Cumprimentos

Bruno Nunes

Olá Bruno Nunes,

Não tenho muito tempo e não aprofundei bem o seu projecto.
No entanto sou da opinião que deve fazer tudo em Access, deve verificar:

a) o que pretende obter (informação desejada)
b) verificar a modelagem dos dados
c) iniciar esses graficos e resultados

E nesta fase sim, se tiver dúvida deve colocar a duvida detalhada do que está acontecer (erro) e o que pretende obter (resultado).

Assim creio que podemos ajudar.

Abraço

Boas,

Após muito estudo e testes, vi que não existe uma forma satisfatória em realizar os cálculos no Excel e retornar para o Access, portanto preciso da ajuda do fórum para realizar os cálculos pelo Access (Não tenho ideia como fazer).

1.) Tenho um trabalho que vai iniciar Dia X e termina em X dias úteis. Preciso calcular a data fim automático. (Feriados de Portugal).
2.) O mesmo trabalho tem XX horas para término divido X dias úteis. Preciso que seja alocado numa tabela o valor das horas por dia, somente em dias úteis.
3.) Preciso fazer a soma total de horas de trabalho de cada dia útil.

Depois desses dados numa tabela, vou gerar um gráfico com total de horas de trabalho dia a dia, a partir do dia atual.

Espero que tenha explicado bem do que preciso.

Preciso de qualquer tipo de ajuda, não sei nem por onde começar.

Cumprimentos,

Bruno Nunes

Olá Bruno,

Estou no celular, deve criar uma base de dados nova e começar a trabalhar o ponto 1.
Fiz uma pesquisa e para o ponto 1 creio que ajude, veja:
https://www.maximoaccess.com/t7479-resolvidocalcular-data-futura-com-dias-uteis-sem-finais-de-semana-e-feriados-nacionais-e-estaduais

Toca a por a mão na massa.

Qualquer coisa estamos cá!

Abraço

Boas,

O ponto 1 foi superado, graças a vossa ajuda.

Agora vem o Ponto 2, preciso que seja parecido ou que faça o mesmo cálculo que a imagem do Excel abaixo:



O Cálculo que preciso é:

Distribuir as horas de trabalho (hr_orc) cada molde por dia útil, conforme a data de entrada e data prevista de termino.
E depois some o total de horas alocadas para cada dia. (Isso será simples após os cálculos por molde, criarei uma consulta crosstab e somarei as horas e depois uma consulta para gerar o gráfico que preciso)

Agradeço as ajudas até o momento.

Cumprimentos,

Bruno Nunes

Olá Bruno,

Estava a trabalhar num exemplo para o ponto 1 (e vou concluir), mas fico feliz por ter resolvido.
Poderia partilhar a sua solução com que resolveu o ponto 1 (pequeno exemplo e simples), lembre-se que a sua dúvida pode ser a mesma de outro colega.
Além disso, como podemos sequer pensar na sua duvida do ponto 2 se não sabemos como tem estruturada e dispõe da informação (para se trabalhar)

Abraço

Olá,

Segue o ficheiro com o que desenvolvi. Bem simples. O modelo é do tópico que me informou.

Agora sobre o ponto 2, tenho o excel que está estruturado.

A aba "Disponibilidade" - Tem o cálculo do total de horas que tenho disponível de mão de obra por dia, menos o total de horas que tenho de funcionários de férias, com isso tenho o total de horas de mão de obra disponível por dia.
A aba "Textura" - Tem a disponibilidade de horas de mão de obra por dia e o total de horas de trabalho em fábrica. (Todo serviço é calculado em horas para ser cobrado anteriormente). Nas primeiras colunas são a base para gerar o gráfico dinâmico.
Nas colunas R:W tenho os dados de cada trabalho (Molde). Coluna R (Numero Molde), coluna S (Horas orçadas), coluna T (Dias Orçados), Coluna U (Horas de trabalho por dia = Dias Orçados / Horas orçadas), Coluna V (data de entrada), Coluna W (data previsão de saída = Ponto 1).

Depois disso vem os cálculos que pretendo fazer no Access, que consiste em identificar a data de inicio (coluna V) na linha 3 e ir distribuindo o valor da coluna U. Somente em dias úteis. Fazendo um teste lógico que o data final da distribuição é igual a data da coluna W e se a soma dos valores distribuídos é igual o valor da coluna S (Horas orçadas).

Após o cálculo por molde em várias linhas, faço a soma do total de horas de trabalho por dia na linha 4. (Os dados desta linha abastece a coluna G).

E Com a coluna G preenchida eu tenho a possibilidade de gerar o gráfico.

Depois de tudo isso você vai pensar, para que você quer isso Bruno? Com o gráfico consigo analisar, se tenho muito trabalho em fábrica e não posso aceitar mais, ou precisamos fazer hora extra, contratar mais funcionários, ou preciso de mais trabalhos (Comercial, vende!), analise de férias, ajuda ao comercial negociar prazo. Só preciso para isso. Planeamento, Planeamento e mais Planeamento!

Já tentei, exportar os dados do Access e importar os dados, sem sucesso.
Já tentei, gerar os gráficos pelo Excel e gerar uma imagem e colar no formulário. Consigo gerar 2 imagens a terceira não gera. E preciso de 3 gráficos.

Caso queiram os outros arquivos das tentativas eu anexo aqui.

Cumprimentos,

Bruno Nunes