Open File name with changing date in name

[Plawrence]

I’m trying to get a vba to open a file daily with a macros and am having trouble doing so. The file is in windows and it’s listed like this “gville grade sheet_12-5-2020” the name stays the same but the date changes.

 

[lrobbo314]

So, that one is 12/5/2020. Would you be running this on 12/6/2020 and looking for the previous date? Or are you wanting to just open the most recent file in the folder?

 

[Plawrence]

So, that one is 12/5/2020. Would you be running this on 12/6/2020 and looking for the previous date? Or are you wanting to just open the most recent file in the folder?

The most recent, it would be imported once daily.

 

[lrobbo314]

How about this?

Sub OpenFile()
Dim Path As String:     Path = "C:\Users\User\Desktop" 'Change to your folder
Dim FSO As Object:      Set FSO = CreateObject("Scripting.FileSystemObject")
Dim Fol As Object:      Set Fol = FSO.getfolder(Path)
Dim b As Boolean:       b = True
Dim eDate As Date

For Each fil In Fol.Files
    If fil.Name Like "gville grade sheet" Then
        If b Then
            eDate = fil.datecreated
            Path = fil.Path
            b = False
        End If
        If fil.datecreated > eDate Then
            eDate = fil.datecreated
            Path = fil.Path
        End If
    End If
Next fil

Workbooks.Open (Path)

End Sub

 

[Plawrence]

How about this?

Sub OpenFile()
Dim Path As String:     Path = "C:\Users\User\Desktop" 'Change to your folder
Dim FSO As Object:      Set FSO = CreateObject("Scripting.FileSystemObject")
Dim Fol As Object:      Set Fol = FSO.getfolder(Path)
Dim b As Boolean:       b = True
Dim eDate As Date

For Each fil In Fol.Files
    If fil.Name Like "gville grade sheet" Then
        If b Then
            eDate = fil.datecreated
            Path = fil.Path
            b = False
        End If
        If fil.datecreated > eDate Then
            eDate = fil.datecreated
            Path = fil.Path
        End If
    End If
Next fil

Workbooks.Open (Path)

End Sub

Looks good, will try and let you know thanks!

 

[Plawrence]

Looks good, will try and let you know thanks!

How about this?

Sub OpenFile()
Dim Path As String:     Path = "C:\Users\User\Desktop" 'Change to your folder
Dim FSO As Object:      Set FSO = CreateObject("Scripting.FileSystemObject")
Dim Fol As Object:      Set Fol = FSO.getfolder(Path)
Dim b As Boolean:       b = True
Dim eDate As Date

For Each fil In Fol.Files
    If fil.Name Like "gville grade sheet" Then
        If b Then
            eDate = fil.datecreated
            Path = fil.Path
            b = False
        End If
        If fil.datecreated > eDate Then
            eDate = fil.datecreated
            Path = fil.Path
        End If
    End If
Next fil

Workbooks.Open (Path)

End Sub

get a run time error 76 on Dim Fol As Object: Set Fol = FSO.getfolder(Path)

 

[lrobbo314]

Did you change the path variable?

 

[Plawrence]

Did you change the path variable?

Dim Path As String: Path = "X:\Gordonsville\Dispatch\Grade Sheet\Gville Grade Sheet"
Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
Dim Fol As Object: Set Fol = FSO.getfolder(Path)
Dim b As Boolean: b = True
Dim eDate As Date

For Each fil In Fol.Files
If fil.Name Like "gville grade sheet" Then
If b Then
eDate = fil.datecreated
Path = fil.Path
b = False
End If
If fil.datecreated > eDate Then
eDate = fil.datecreated
Path = fil.Path
End If
End If
Next fil

Workbooks.Open (Path)


this is what i got....

 

[lrobbo314]

I don't know. The code works on this end. That's a 'path not found' error. I would make sure there are no typos in the path, but outside of that, I'm not sure.

 

[Plawrence]

I don't know. The code works on this end. That's a 'path not found' error. I would make sure there are no typos in the path, but outside of that, I'm not sure.

thanks for your help I still cant get it to work, but thanks for trying...