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
 

KhallP

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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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
You didn't answer my questions.
 

KhallP

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

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

Code:
Private Sub Workbook_Open()

Call AutoRunMacro

End Sub
 

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 which workbook is this code in?

Also note, as before:

Rich (BB code):
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

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

ADVERTISEMENT

And which workbook is this code in?

Also note, as before:

Rich (BB code):
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

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 is it the same workbook as the one the code was trying to open originally, which was also called teste.xls?
 

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

ADVERTISEMENT

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

KhallP

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

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
Remove this:

Code:
myMostRecentFile As String,

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

Code:
Dim myMostRecentFile As String
 

KhallP

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

Code:
myMostRecentFile As String,

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

Code:
Dim myMostRecentFile As String
even so it doesn't work and the error continues, I don't know what to do more ...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,920
Latest member
Riskyplan

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