Comment, please, did I get an adequate algorithm? I believe that it can be improved or at least optimized (

I thought in the direction of regexp, but there was no idea how to screw it in here (

Essence: there are files in the folder at the specified address, you need to find the current one (the number corresponds to the counter of a higher cycle - i). The problem is that before this i can have one more, two or three zeros. And depending on this, different consequences occur)

If (i < 10 And Dir$(folderAdress$ + "TNV - 000" & i & "-1.xls") <> "" And nextsl = True) Then Workbooks.Open folderAdress$ & "TNV - 000" & i & "-1.xls" outTNVfilename = "ТНВ - 000" & i & "-1.xls" tk = "/1" nextsl = False u = i ElseIf (i < 10 And Dir$(folderAdress$ + "TNV - 000" & u & "-2.xls") <> "" And nextsl = False) Then Workbooks.Open folderAdress$ & "TNV - 000" & u & "-2.xls" outTNVfilename = "ТНВ - 000" & u & "-2.xls" tk = "/2" nextsl = True i = u ElseIf (i < 10 And Dir$(folderAdress$ + "TNV - 000" & i & ".xls") <> "") Then Workbooks.Open folderAdress$ & "TNV - 000" & i & ".xls" outTNVfilename = "ТНВ - 000" & i & ".xls" tk = "" ElseIf (i > 9 And i < 100 And Dir$(folderAdress$ + "TNV - 00" & i & "-1.xls") <> "" And nextsl = True) Then Workbooks.Open folderAdress$ & "TNV - 00" & i & "-1.xls" outTNVfilename = "ТНВ - 00" & i & "-1.xls" tk = "/1" nextsl = False u = i ElseIf (i > 9 And i < 100 And Dir$(folderAdress$ + "TNV - 00" & u & "-2.xls") <> "" And nextsl = False) Then Workbooks.Open folderAdress$ & "TNV - 00" & u & "-2.xls" outTNVfilename = "ТНВ - 00" & u & "-2.xls" tk = "/2" nextsl = True i = u ElseIf (i > 9 And i < 100 And Dir$(folderAdress$ + "TNV - 00" & i & ".xls") <> "") Then Workbooks.Open folderAdress$ & "TNV - 00" & i & ".xls" outTNVfilename = "ТНВ - 00" & i & ".xls" tk = "" ElseIf (i > 99 And i < 1000 And Dir$(folderAdress$ + "TNV - 0" & i & "-1.xls") <> "" And nextsl = True) Then Workbooks.Open folderAdress$ & "TNV - 0" & i & "-1.xls" outTNVfilename = "ТНВ - 0" & i & "-1.xls" tk = "/1" nextsl = False u = i ElseIf (i > 99 And i < 1000 And Dir$(folderAdress$ + "TNV - 0" & u & "-2.xls") <> "" And nextsl = False) Then Workbooks.Open folderAdress$ & "TNV - 0" & u & "-2.xls" outTNVfilename = "ТНВ - 0" & u & "-2.xls" tk = "/2" nextsl = True i = u ElseIf (i > 99 And i < 1000 And Dir$(folderAdress$ + "TNV - 0" & i & ".xls") <> "") Then Workbooks.Open folderAdress$ & "TNV - 0" & i & ".xls" outTNVfilename = "ТНВ - 0" & i & ".xls" tk = "" End If
  • So: 01,001,0001? - Alexey Lobanov

1 answer 1

It is necessary to break into smaller functions to make the code visible. The problem in your code is rather in numerous repetitions and the associated possibility of inconsistent behavior. Pseudocode:

 sub getBaseFilename(i) dim r$ as string r$ = Str$(i) while (len(r$) < 4) r$ = "0" & r$ return "TNV - " & r$ sub exists(name$) return Dir$(folderAdress$ & name$) <> "" sub process(i) found = False baseName$ = getBaseFilename(i) name$ = baseName$ & "-1.xls" if (exists(name$)) tk = "/1" nextsl = False u = i if (not found) name$ = baseName$ & "-2.xls" if (exists(name$)) tk = "/2" nextsl = True i = u found = True if (not found) name$ = baseName$ & ".xls" if (exists(name$)) tk = "/2" found = True if (found) outTNVfilename = name$ Workbooks.Open folderAdress$ & name$