I was writing a macro with which I wanted to copy information from various Excel files and paste then in a created one. Basically, I need to check the existence of files from certain areas from my company in a group up to 1000 possible files (not counting for their possible updates) and, if there is one, to copy the last update of it and paste it in the main one.
My solution for it was to use a inputbox to get the reference to the area I want (its name is in the file's names) and use a backward loop (from 50 to 0 by -1) to check for the last update (each update of the file is saved with its respective number). Inside the loop, if the last version was found, it would do what I wanted and, if not, an ErrorHandler was used to jump to the next possible case.
Basically:
For qntd_pa = 1 To 999 ‘loop for number of documents’
If qntd_pa < 10 Then
ctrl_qntd_pa = ".00" & CStr(qntd_pa)
Else
If qntd_pa < 100 Then
ctrl_qntd_pa = ".0" & CStr(qntd_pa)
Else
ctrl_qntd_pa = "." & CStr(qntd_pa)
End If
End If
For updates = 50 To 0 Step -1 'loop for updates'
If updates = 0 Then
ctrl_atual = ""
'ctrl_atual is used to define the update version of the file inside its name’
Else
If updates < 10 Then
ctrl_atual = "-0" & CStr(updates)
Else
ctrl_atual = "-" & CStr(updates)
End If
End If
On Error GoTo Ctrl_Errors:
Set Planilha_Ativa = Workbooks.Open(address & "Document_number" & ctrl_qntd_pa & "-" & area & ctrl_atual & ".xlsx")
‘address is a previously set variable in which the location of the files is set; area is a previously set string defining the name of an area that could had received the document’
Set Planilha_Ativa = ActiveWorkbook
On Error GoTo 0
COPY AND PASTE CODE
ActiveWorkbook.Close SaveChanges:=False
updates = 0
Update_Error_Corrector:
Next updates
Next qntd_pa
MORE CODE
Exit Sub
Ctrl_Errors:
Resume Update_Error_Corrector:
End Sub
When I tested the code in my personal computer, everything went smoothly, that is, the macro would start trying to find the 50th version of document 001 from area ABCD. If not found, it would result in a error, activating the error handler, jumping for the next possible update of the document (49th) and so on. If not found until the first version (no update from the document), it would mean that document 001 wasn't directed to area ABCD, so the macro would jump to document 002 and try to find the 50th version of document 002 from area ABCD and so until document 999.
Managing to make it work as I wanted, I saved the xlsm file in the corporative directory of my company and that's when the trouble began. Basically, when the file was not found, instead of automatically jumping to the next possibility, an alert would appear stating that such file didn't exist, for which I would need to confirm (press ok) to the macro to continue.
Because this would be impractical (up to 50*1000 alerts), I solved it by using a DisplayAlerts = False inside the loop. While it solved the problem of having to confirm the alert, making the macro totally automatic, it increased its runtime by approximately 30 times (in my personal directory (C:), it runs in around a minute and a half; in the corporative directory (K:) it takes around 43 minutes to end).
Since I'm not a programmer (I'm an economics major), I'm not exactly sure if there is something intrinsic to corporative directories that makes the alert show (since it doesn't appear when I run it in my personal directory and in my personal computer). Is there a way to work by it without the DisplayAlerts so it can run faster?
Thanks in advance
Aucun commentaire:
Enregistrer un commentaire