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]

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

the file I mentioned in the post is with the extension "xlsm" could it be that?

 

[KhallP]

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

and how can I "activate" it?

 

[RoryA]

It would be better to refer to it specifically by name or using a variable if possible. Your code seems to have changed from the original code you posted (which wouldn't have run), so perhaps you could post the full code as it actually is now.

 

[KhallP]

It would be better to refer to it specifically by name or using a variable if possible. Your code seems to have changed from the original code you posted (which wouldn't have run), so perhaps you could post the full code as it actually is now.

The complete code is this only:

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

 

[RoryA]

Since you're opening the workbook in your code, assign it to a variable and use that rather than activeworkbook.