0

I'm trying to get a macro to work as a version history tool. I think I'm using basic methods here and there might be better ones out there but I think it's doable nonetheless. The error I get is "Subscript out of range". The culpript is the second to last line of my code, which is where I save a copy of the file. So this is my code:

Sub Historian()

Dim filePath As String
Static counter As Integer
Dim fileName As String

filePath = "A:\Downloads A\Ex_Files_Learning_VBA_Excel\Exercise Files\Ch05\WbkBackup"

For counter = 0 To 10
    Workbooks.Open (filePath & counter)
    On Error GoTo Handler:
Next counter

MsgBox ("counter has reached 10")

Exit Sub

Handler:
ThisWorkbook.Activate
fileName = ThisWorkbook.FullName
Workbooks(fileName).SaveCopyAs fileName:=(filePath & counter)
MsgBox ("ok, last version was: " & counter)

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
agasa
  • 1

2 Answers2

0

On Error GoTo Handler

should be placed before

Workbooks.Open (filePath & counter)

which should be:

Workbooks.Open (filePath & "\" & split(Thisworkbook.Name, ".")(0) & counter & "." & split(Thisworkbook.Name, ".")(1))

In fact, your code should look in this way:

Sub Historian()
 Dim filePath As String, fileName As String, strExt As String
 Static counter As Long
 
 filePath = "C:\Teste VBA Excel\PROG BACKUP" ' "A:\Downloads A\Ex_Files_Learning_VBA_Excel\Exercise Files\Ch05\WbkBackup"
 fileName = Split(ThisWorkbook.Name, ".")(0)
 strExt = Split(ThisWorkbook.Name, ".")(1)

 For counter = 0 To 10
    On Error GoTo Handler:
    Workbooks.Open (filePath & "\" & fileName & counter & "." & strExt)
 Next counter

 MsgBox ("counter has reached 10")

 Exit Sub

Handler:

 ThisWorkbook.SaveCopyAs fileName:=(filePath & "\" & fileName & counter & "." & strExt)
 MsgBox ("ok, last version was: " & counter)

End Sub

But I think that checking the existing of the workbook by opening it, is not so appropriate way. I would suggest you to replace

On Error GoTo Handler:
Workbooks.Open (filePath & "\" & fileName & counter & "." & strExt)

with

If Dir(filePath & "\" & fileName & counter & "." & strExt) = "" Then GoTo Handler
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
0

Workbooks(filename) do not take the filename full path as argument, only the filename. Change Workbooks(filename) to ThisWorkbook as suggested by Warcupine

To improve your code, I would suggest you not to test the opening of Workbook. This is long and then you have to close the workbooks you opened... Instead you could use the Dir() function: Check if the file exists using VBA

Sylvain
  • 1
  • 3