2
Good night.
To the experts in vba, I have been working in several spreadsheets with this code logic, to import files, and after that, work on their numbers with either macro or formulas.
I would like to receive tips to improve this code, line reduction, whatever. Any hint is welcome!
Thank you.
Sub AtualizarRelatorioGeral()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
SaveChanges = False
Dim Arquivo(18) As String
Arquivo(1) = "zpp03ontem"
Arquivo(2) = "vl10a"
Arquivo(3) = "mb51consumomensal"
Arquivo(4) = "mb51repassegerado"
Arquivo(5) = "mb52peixerev"
Arquivo(6) = "mb52peixepro"
Arquivo(7) = "mb52exp"
Arquivo(8) = "mb52repassesaldo"
Arquivo(9) = "zsd17"
Arquivo(10) = "zsd25fat"
Arquivo(11) = "zsd25dev"
Arquivo(12) = "mc.9estoquecd"
Arquivo(13) = "mc.9consumo"
Arquivo(14) = "mc.9centro"
Arquivo(15) = "mc.9cdhipet"
Arquivo(16) = "mc.9valor"
Arquivo(17) = "zpp25"
Arquivo(18) = "mc.9produto"
For i = 1 To 18
Sheets(Arquivo(i)).Visible = True
Next i
Set WBgeral = ActiveWorkbook
'IMPORTAR ARQUIVOS
For i = 1 To 18
WBgeral.Activate
Sheets(Arquivo(i)).Activate
Cells.Select
Selection.Clear
Workbooks.OpenXML ("C:\macrosm\prerelatoriolucimara\" & Arquivo(i) & ".xls")
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
WBgeral.Activate
Sheets(Arquivo(i)).Activate
ActiveSheet.Paste
Workbooks(Arquivo(i)).Close SaveChanges:=False
Next i
'IMPORTAR ARQUIVOS
Sheets("Principal").Activate
For i = 1 To 18
'Sheets(Arquivo(i)).Visible = False
Next i
Cells(4, 16).Value = Date
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
SaveChanges = True
End Sub
You are performing many interactions between the VBA and the Excel spreadsheet and this is slow. For example, avoid using . Select/ Activate. You are also opening several files, so this is slow even... There is an SE site for these types of questions: the Code Review
– danieltakeshi
Nice codereview, I didn’t know... but taking advantage of the question I think that if you separated each part of your code into different functions each one doing its part (Divide and Conquer) and instead of having the name of the files in the code, you could have a specific folder (if possible), to read everything in that folder, for example folder carry, loaded, everything read correctly goes to "loaded" folder. But only one idea even.
– Evert
Cross-post: Importing files into Excel
– danieltakeshi