The program does not detect a specific folder

[KhallP]

I am trying to open the most recent file in a specific folder but when I run the program an error appears and says that the folder does not exist (but it does exist), can someone help me?


Code:

Private Sub WriteValues()

    Workbooks.Open (myMostRecentFile)
    Workbooks(myMostRecentFile).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\Andre\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

Original Path:

Error:

Translation:

We are sorry but we were unable to locate D: \ Users \ AFCALME \ Documents \ Projeto_Luis \ Andre \ EEC \ QEIM \ QEIM_geral.
Is it possible that it has been moved, deleted or that its name has been changed?



Error Line:

 

[KhallP]

So, which routine are you actually running, which workbook is the code in, and is the file you are trying to open currently closed?

I just tried to use the code with other locations and I just realized that the error occurs for all specified paths, you know what it could be?

 

[RoryA]

You didn't answer my questions.

 

[KhallP]

You didn't answer my questions.

Sry, there is my complete code, and yes, the file I'm trying to open is closed, that's why I'm trying to open it ahah


Module:

Private Sub WriteValues()

    Workbooks(myMostRecentFile).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

Workbook

Private Sub Workbook_Open()

Call AutoRunMacro

End Sub

 

[RoryA]

And which workbook is this code in?

Also note, as before:

Private Sub WriteValues()

    Workbooks(myMostRecentFile).Sheets("QEIM C21").Select

the highlighted variable has no value in this code. It was declared in the recentFilesSpecificFolder routine and only exists there.

 

[KhallP]

And which workbook is this code in?

Also note, as before:

Private Sub WriteValues()

    Workbooks(myMostRecentFile).Sheets("QEIM C21").Select

the highlighted variable has no value in this code. It was declared in the recentFilesSpecificFolder routine and only exists there.

the name of the workbook that has the code is "Teste.xls"


I'm not very good with functions that return a value, could you help me bring that value out of the procedure so that I can use it in my "WriteValues"?

 

[RoryA]

So is it the same workbook as the one the code was trying to open originally, which was also called teste.xls?

 

[RoryA]

Also remember you cannot open two workbooks with the same name, even if they are not the same workbook.

 

[KhallP]

Also remember you cannot open two workbooks with the same name, even if they are not the same workbook.

I tried to open a new file for "myMostRecentFile" not being "Teste.xls" but as you said in the "WriteValues" procedure the "myMostRecentFile" does not have an assigned value, it could help me to make the value of the most recent file was assigned to "myMostRecentFile"?

 

[RoryA]

Remove this:

myMostRecentFile As String,

from the declarations in the recentfile routine, and at the top of the module, before all routines, add:

Dim myMostRecentFile As String

 

[KhallP]

Remove this:

myMostRecentFile As String,

from the declarations in the recentfile routine, and at the top of the module, before all routines, add:

Dim myMostRecentFile As String

even so it doesn't work and the error continues, I don't know what to do more ...