Subscript out of range (Error)

[KhallP]

I created a procedure to detect and open the last file inserted in a specific folder
when I run the program an error is displayed, can someone help me?



The code:

Private Sub WriteValues()


Private Sub recentFilesSpecificFolder()
Dim myFile As String, myMostRecentFile As String, myRecentFile As String, myDirectory As String, fileExtension As String
Dim recentDate As Date

myDirectory = Environ("userprofile") & "\Documents\Projeto_Luis\André\EEC\QEIM\QEIM_geral"
fileExtension = " * .xls"

If Right(myDirectory, 1) <> "\" Then myDirectory = myDirectory & "\"

myFile = Dir(myDirectory & fileExtension)
If myFile <> "" Then
    myRecentFile = myFile
    recentDate = FileDateTime(myDirectory & myFile)
    Do While myFile <> ""
        If FileDateTime(myDirectory & myFile) > recentDate Then
             myRecentFile = myFile
             recentDate = FileDateTime(myDirectory & myFile)
         End If
         myFile = Dir
    Loop
End If
myMostRecentFile = myRecentFile
Workbooks.Open Filename:=myDirectory & myMostRecentFile
End Sub


End Sub

The error:

Note: I forgot to mention, the code is executed when the exel file is opened

 

[KhallP]

 

[RoryA]

I can't see any line there that would cause that error. Which line is highlighted when you debug?

 

[RoryA]

Is there code in the Workbook_Open event of the workbook you are opening? I think the error is in that code.

 

[KhallP]

Yes there is, here it is

Private Sub Workbook_Open()

Call AutoRunMacro

End Sub

 

[RoryA]

What's the code for AutoRunMacro? Simple thing to do: in the VB Editor, click Tools - Options, then on the General tab change the error handling setting to 'Break in class module'. Now run your original code again, and it should break on the actual error line.

 

[KhallP]

Is there code in the Workbook_Open event of the workbook you are opening? I think the error is in that code.

In the meantime a different error arose, I believe that I am not doing what I want in the right way, as I said I want to open the last changed / created file in a specific folder, then I want to assign values in the cells of that file, I am not yet very experienced in VBA and I think that the problem is with the code, could you help me?

Private Sub WriteValues()

    ActiveWorkbook.Sheets("QEIM C21").Select
    ActiveCell = Range("AC12").Select

    If ActiveCell.Value = "" Then
        ActiveCell.Select
        With Selection
            .Name = "Date"
            .HorizontalAlignment = xlRight
        End With

        Range("AD12").Select
        With Selection
            .Name = "Time"
            .HorizontalAlignment = xlRight
        End With

        Range("AE12").Select
        With Selection
            .Name = "Value"
        End With

        Else
        End If


    Range("AC13").Select

    If ActiveCell.Value = "" Then
        Call FillCells

    Else
        Do Until ActiveCell.Value = ""
            ActiveCell.Offset(1, 0).Select
        Loop
            Call FillCells

End If
End Sub



Sub FillCells()

    ActiveCell.Value = Range("C13").Value
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = WorksheetFunction.Sum(Range("W13:W108"), Range("AA13:AA108"))
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = "kWh"
   
End Sub



Private Sub recentFilesSpecificFolder()
Dim myFile As String, myMostRecentFile As String, myRecentFile As String, myDirectory As String, fileExtension As String
Dim recentDate As Date

myDirectory = Environ("userprofile") & "\Documents\Projeto_Luis\André\EEC\QEIM\QEIM_geral"
fileExtension = " * .xls"

If Right(myDirectory, 1) <> "\" Then myDirectory = myDirectory & "\"

myFile = Dir(myDirectory & fileExtension)
If myFile <> "" Then
    myRecentFile = myFile
    recentDate = FileDateTime(myDirectory & myFile)
    Do While myFile <> ""
        If FileDateTime(myDirectory & myFile) > recentDate Then
             myRecentFile = myFile
             recentDate = FileDateTime(myDirectory & myFile)
         End If
         myFile = Dir
    Loop
End If
myMostRecentFile = myRecentFile
Workbooks.Open Filename:=myDirectory & myMostRecentFile
End Sub



Sub AutoRunMacro()
       
Call recentFilesSpecificFolder
Call WriteValues

End Sub

Sorry for not specifying it before

 

[RoryA]

You've got the sheet name wrong, or the wrong workbook active if you get a subscript error on that line.

 

[KhallP]

You've got the sheet name wrong, or the wrong workbook active if you get a subscript error on that line.

FilePath: D:\Users\AFCALME\Documents\Projeto_Luis\André\EEC\QEIM\QEIM_geral

Sheet of the other file:

everything is apparently correct ...

 

[RoryA]

Than I guess that workbook is not active at the time you run the code.