Otimizar recuros - evitar travamento

Wagner.cwb

26-12-2016 22:04:31

Boa tarde Caros!
Um feliz Natal a todos (um pouco atrasado) e um próspero ano novo!

Preciso da ajuda de vocês para ajustar o código que estou editando para fazer um "tarifador" de contas telefônicas.

O processo é abrir os arquivos de uma pasta (possuí todo o período tarifário) e exporta para duas planilhas, a primeira que realiza os cálculos e a segunda que consolida os valores.

Primeiramente, necessita da ajuda de voces para identificar o que pode estar consumindo muito recursos no código, pois o script realiza tudo perfeitamente, só sobrecarrega e trava tudo a cada 5 dias apurados...

Há algo que posso melhorar para otimizar o processo?

Código: Selecionar todos

Private Function gfLinhaAtual() As String
    Application.Volatile
 
    gfLinhaAtual = ActiveCell.Address
End Function
Sub Abrir_Copiar_Colar()

'http://comunidade.itlab.com.br/eve/forums/a/tpc/f/273606921/m/5797050083
'http:/www.vbaexpress.com/forum/showthread.php?312-Solved-Clear-Clipboard-Contents
'http://guiadoexcel.com.br/funcao-para-retornar-a-celula-ativa-do-excel/


Dim FSO As Object
Dim Pasta As String
Dim Planilha As Object
Dim OpenBook As String
Dim sName As String
Dim cont As String
Dim base As Workbook

'cont = 0

'Para a macro executar mais rápido!
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    
  
End With

'abrir planilha base
Set base = Workbooks.Open("\\p451992\d$\OI\Tarifador\Chamadas 18-10 a 17-11_2016\_Custo_Ilhas1_2016_05_NOVA.xlsx")
Set FSO = CreateObject("Scripting.FileSystemObject")
Pasta = "\\p451992\d$\oi\tarifador\Chamadas 18-10 a 17-11_2016" 'Pasta com as planilhas que serão abertas e copiadas


Application.Calculation = xlCalculationManual

'inicia o loop para abrir arquivos
For Each Planilha In FSO.GetFolder(Pasta).Files

'busca por extensão do arquivo
If InStr(1, Planilha, ".csv") = 0 Then GoTo PRÓXIMO

'ABRIR PLANILHA
Workbooks.Open (Planilha)

sName = VBA.Mid(Planilha, 55, 4)

'anteriormente salvava os arquivos da extensão rpt para csv, agora as macros são distintas.
'ActiveWorkbook.SaveAs Filename:="\\p451992\d$\oi\tarifador\Chamadas 18-10 a 17-11_2016\" & sName & ".xlsm", FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

'incia a edição no arquivo (dias.csv)
'selecionando dados
Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
 

'copiar dados (dias) para as planilha de calculo
    Windows("Modelo_2017_v1_06.xlsm").Activate
    Range("A2").Select
    ActiveSheet.Paste '–> código para colar
    Application.CutCopyMode = False '–> código para tirar a seleção de cópia (ganho na memória da máquina)
        
    'PADRONIZAR DADOS IMPORTADOS - texto para colunas, classificar ordem
    Call Módulo6.Padronizar
    
    'FATURAMENTO - executa
   Range("A2").Select
   Selection.End(xlDown).Select
       
    'Dim i2 As String
  
    Calculate
    
    i = VBA.Right(gfLinhaAtual, 6)
    
    'outra forma de achar a ultima linha
    'i2 = 2
    'Do While Range("A" & i2).Value <> ""
   ' i2 = i2 + 1
    'Loop
       
    'EXPANDIR OU NAO O CALCULO - equivale ao duplo clique para copiar ate ultima linha
    If Range("A" & i).Value <> "" And Range("N" & i).Value = "" Then
           
    Range("N3:X3").Select
    Selection.AutoFill Destination:=Range("N" & 3 & ":" & "X" & i)
    'Range("N" & 3 & ":" & "X" & i).Select
    'Range("O2").Select
    Calculate
    Else
    
    Calculate
    
    End If
    
    
    'FILTRAR CHAMADAS
    
   ActiveSheet.Range("S" & 1 & ":" & "S" & i).AutoFilter Field:=19, Criteria1:=">0.0"
   
   'COPIAR DADOS FILTRADOS PARA OUTRA PLANILHA
   
    Range("X16").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Selection.Copy
    
    'ativa planilha base  - consolidar dados
    base.Activate
        
    'ENCONTRAR ULTIMA LINHA PREENCHIDA
    Range("A1048576").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select 'PULA UMA LINHA ANTES DE COLAR
    
    'COLAR DADOS
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Windows("Modelo_2017_v1_06.xlsm").Activate
    
    Application.CutCopyMode = False '–> código para tirar a seleção de cópia (ganho na memória da máquina)
       
     'RETORNAR PARA PLANILHA MODELO
     
     Windows("Modelo_2017_v1_06.xlsm").Activate
     
     'LIMPAR CAMPOS
     Call Módulo2.Limpar
     Call ClearClipboard
       
'FECHAR PLAN DADO DIÁRIO

Workbooks(sName).Close False

Application.CutCopyMode = False
    
'Workbooks(OpenBook).Close False
PRÓXIMO:
Next



MsgBox "Dados Copiados com Sucesso!", vbInformation, "Aviso"

'Application.Calculation = xlCalculationAutomatic

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True

End With

End Sub
Obrigado

webmaster

29-12-2016 15:52:05

Wagner,

O codigo parece bom, pelo menos na minha visao. O problema eh o VBA nao ter multithread, o que lhe permitiria aplicar paralelizacao em pelo menos um parte do codigo. Existem algumas abordagens por ahi, mas nao arriscaria.

O que posso recomendar eh:

Se nao precisa escrever nos arquivos que esta abrindo, abra-os como somente-leitura.
Evite rotinas de interacao com a planilha, como essa:

Código: Selecionar todos

Range("A2").Select
   Selection.End(xlDown).Select
O select aqui eh o problema. Evite ao maximo, se puder. De resto, eh multithread. Se quiser arriscar:

http://www.excelhero.com/blog/2010/05/m ... d-vba.html

Wagner.cwb

29-12-2016 20:57:36

Oi Tomas, obrigado pelo seu comentário.
Achei muito boa a sua indicação de trabalhar com Multithreading, rodei o exemplo do site indicado, achei fantástico a velocidade em que executou a captura dos dados, ainda permitindo a manipulação simultânea da planilha.
Bom, mas vou precisar estudar bastante o assunto, mas é algo bem interessante mesmo.

Já quanto aos "Select"s, a princípio não sei como me livrar deles... de repente posso realizar em mais partes a tarefa, penso agora em criar uma etapa de padronização dos dados e depois outra etapa para consolidá-los.
Os arquivos principais (dados diários) estão no formato "rpt", não conhecia esta extensão, mas vi diferença nenhuma do "csv", a única diferença é o tamanho do arquivo, em rpt é 40Mb, enquanto em csv fica em 7Mb, mas percebi que piorou o tempo de execução, de 3 min por arquivo passou para 5 a 6... não compreendi o motivo, vou tentar realizar o teste a partir da sua recomendação de "somente leitura".
Uma pergunta meio tosca, mas não consigui fazer sozinho... minha planilha esta com 1048576 linhas, sendo que não irei ocupar nem metade disso, como faço para editar? Já tentei criar uma nova e mesmo assim esta com as linhas completas...


Obrigado! abs!

Wagner.cwb

02-01-2017 23:15:31

Saudações! feliz ano novo a todos!

Tomas, segui as suas recomendações e obtive sucesso, finalmente consegui concluir todo o processo.
Fragmentei as etapas e transformei os arquivos de base para somente leitura e obtive um bom ganho de desempenho.

Estava lendo algumas dicas para liberar mais memória cache e encontrei este artigo:

http://www.techtudo.com.br/dicas-e-tuto ... -o-pc.html

Gostaria de aprender de como aplicar este comando dentro do VBA ou de fazê-lo abrir o arquivo atalho que contem o comando.

Código: Selecionar todos

%windir%\system32\rundll32.exe advapi32.dll,ProcessIdleTasks
A princípio utilizei o artigo do Tomas:

http://www.tomasvasquez.com.br/blog/mic ... ute-no-vba

Código: Selecionar todos

Const SW_SHOW = 1
Const SW_SHOWMAXIMIZED = 3
Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _
                      (ByVal hwnd As Long, _
                      ByVal lpOperation As String, _
                      ByVal lpFile As String, _
                      ByVal lpParameters As String, _
                      ByVal lpDirectory As String, _
                      ByVal nShowCmd As Long) As Long
Sub AbrirArquivo()
    On Error Resume Next
    Call ShellExecute(0, "open", "C:\Users\usuario\Desktop\LimparCache.lnk", "argumentos", _
                          "pasta onde está localizado o arquivo", _
                          SW_SHOWMAXIMIZED)
End Sub
Por favor, podem me ajudar nesta tarefa?

Obrigado,

abs

webmaster

04-01-2017 16:48:59

Wagner,

Dei uma olhada nessa chamada e acho que nao eh bem o que procura:

http://www.vbmania.com.br/index.php?mod ... &id=395964
http://www.thewindowsclub.com/misconcep ... sidletasks

Wagner.cwb

05-01-2017 22:08:57

Obrigado Tomas, tem um discussão interessante sobre o assunto.
webmaster escreveu:Wagner,

Dei uma olhada nessa chamada e acho que nao eh bem o que procura:

http://www.vbmania.com.br/index.php?mod ... &id=395964
http://www.thewindowsclub.com/misconcep ... sidletasks
Por gentileza, enquanto estudo outras alternativas, confirme-me se executei corretamente o código?

No
C:\Users\usuario\Desktop\LimparCache.lnk
é um atalho que contem o comando
%windir%\system32\rundll32.exe advapi32.dll,ProcessIdleTasks
, pois não sei outra forma de executar o comando diretamente no código vba.
A princípio parece que funciona, pois não consta nenhuma mensagem de erro.

Código: Selecionar todos

Const SW_SHOW = 1
Const SW_SHOWMAXIMIZED = 3
Public Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _
                      (ByVal hwnd As Long, _
                      ByVal lpOperation As String, _
                      ByVal lpFile As String, _
                      ByVal lpParameters As String, _
                      ByVal lpDirectory As String, _
                      ByVal nShowCmd As Long) As Long
Sub AbrirArquivo()
    On Error Resume Next
    Call ShellExecute(0, "open", "C:\Users\usuario\Desktop\LimparCache.lnk", "argumentos", _
                          "pasta onde está localizado o arquivo", _
                          SW_SHOWMAXIMIZED)
End Sub
Obrigado!