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:

 

[RoryA]

Is the folder called Documents or Documentos on your system?

 

[KhallP]

Is the folder called Documents or Documentos on your system?

"Documentos" is Portuguese because I am from Portugal but when I click on the location bar the following appears: "D: \ Users \ AFCALME \ Documents \ Projeto_Luis \ Andre \ EEC \ QEIM \ QEIM_geral". In other words, in English, which is the one I have always used for all projects

 

[RoryA]

Open one of the files manually, then in the VB Editor, in the Immediate window, type:

?Activeworkbook.path

and check that against what is in the code.

Also note that you don't need spaces around the wildcard here:

fileExtension = " * .xls"

and the logic of your code means that it will try and open a workbook even if there wasn't an .xls file in the folder.

 

[KhallP]

Open one of the files manually, then in the VB Editor, in the Immediate window, type:

?Activeworkbook.path

and check that against what is in the code.

Also note that you don't need spaces around the wildcard here:

fileExtension = " * .xls"

and the logic of your code means that it will try and open a workbook even if there wasn't an .xls file in the folder.

Yes I know, but as I am working in a company, there are always files in this folder and all of them are ".xls"


Result of Immediate Window:

 

[RoryA]

Before your line that opens the workbook, please add:

Msgbox "Path: " & myDirectory & vbcrlf & "File: " & myMostRecentFile

and verify those values look correct.

 

[KhallP]

Before your line that opens the workbook, please add:

Msgbox "Path: " & myDirectory & vbcrlf & "File: " & myMostRecentFile

and verify those values look correct.

I think the value is correct...

 

[RoryA]

And it's definitely that line that is causing the problem? I note that you have another workbooks.open line at the start of your first sub that will never work as it uses a variable that is not in scope.

 

[KhallP]

And it's definitely that line that is causing the problem? I note that you have another workbooks.open line at the start of your first sub that will never work as it uses a variable that is not in scope.

I ended up taking it out and still giving the same error ..

 

[RoryA]

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