The program does not detect a specific folder

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
64
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is the folder called Documents or Documentos on your system?
 

KhallP

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
64
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Before your line that opens the workbook, please add:

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

and verify those values look correct.
 

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
64
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
So, which routine are you actually running, which workbook is the code in, and is the file you are trying to open currently closed?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,552
Messages
5,636,969
Members
416,953
Latest member
broexc

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
Top