The program does not detect a specific folder

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
157
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA 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:
Capturar.JPG




Error:


Capturar1.JPG


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:

2.JPG
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Is the folder called Documents or Documentos on your system?
 
Upvote 0
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
 
Upvote 0
Open one of the files manually, then in the VB Editor, in the Immediate window, type:

Code:
?Activeworkbook.path

and check that against what is in the code.

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

Code:
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.
 
Upvote 0
Open one of the files manually, then in the VB Editor, in the Immediate window, type:

Code:
?Activeworkbook.path

and check that against what is in the code.

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

Code:
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:


Capturar.JPG
 
Upvote 0
Before your line that opens the workbook, please add:

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

and verify those values look correct.
 
Upvote 0
Before your line that opens the workbook, please add:

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

and verify those values look correct.

I think the value is correct...


Capturar.JPG
 
Upvote 0
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.
 
Upvote 0
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 ..
 
Upvote 0
So, which routine are you actually running, which workbook is the code in, and is the file you are trying to open currently closed?
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,147
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top