Opening Working book using vba

[Nichole09]

Hello!! I have the below code to attempt to open the latest workbook with a certain name. For some reason, if the current date is not found the below code should keep subtracting 1 day from today's date and keep looking. But when I run the code, it only looks for today's date over and over again. I cant understand why it is not recognizing the updated date. Please help! I would greatly appreciate it!

Sub compare()
Dim last As Long
Dim filename As String, myfile As String
Dim strfile As String, dtfile As Date
Dim current As Integer, getweeknumber As Integer


Set ws1 = Workbooks("Review").Sheets("Ops")


current = DatePart("q", ws1.range("d8").Value, 2)
getweeknumber = Int((13 + Day(ws1.range("d8").Value) - Weekday((ws1.range("d8").Value), vbMonday) - 5) / 7)

If current = 1 And getweeknumber = 2 Then
    myfile = "MT.WesternMontana_"
ElseIf current = 1 And getweeknumber > 3 Then
myfile = "WY.GreaterWyoming_"
ElseIf current = 2 And getweeknumber = 2 Then
myfile = "WY.CheyenneWyoming_"
ElseIf current = 2 And getweeknumber > 3 Then
myfile = "SD.SouthDakota-MT.Montana_"
ElseIf current = 3 And getweeknumber = 2 Then
myfile = "OR.Oregon-WA.Washington_"
ElseIf current = 3 And getweeknumber > 3 Then
myfile = "ID.Idaho-WA.Washington_"
Else
End If


dtfile = Date
'dtfile = dateadd("m" -1, now())
' use the above comment if need to look back a month


filename = "G:\Administration\Review\" & myfile & Format(dtfile, "mmddyyyy") & ".xlsx"

If Len(filename) = 0 Then
MsgBox "No Files were found.", vbExclamation
Exit Sub
End If

Do While Len(filename) > 0
On Error Resume Next
Set wbk = Workbooks.Open(filename)
If wbk Is Nothing Then
dtfile = dtfile - 1
Else
End If
On Error GoTo 0
Loop



End Sub

 

[CSmith]

Even if it were to be able to open the file, it looks like it will never exit the Do While loop.

 

[Legacy 456155]

I haven't studied your code, but at first glance, you have a conditional exit sub that will always ring true:

filename = "G:\Administration\Review\" & myfile & Format(dtfile, "mmddyyyy") & ".xlsx"

If Len(filename) = 0 Then
    MsgBox "No Files were found.", vbExclamation
    Exit Sub
End If

Len(filename) = 0
is perpetually true.

 

[GWteB]

Your filename never changes, so replace the last bit of your code with ...

filename = "G:\Administration\Review\" & myfile & Format(dtfile, "mmddyyyy") & ".xlsx"

If Len(filename) = 0 Then
MsgBox "No Files were found.", vbExclamation
Exit Sub
End If

Do While Len(filename) > 0
    On Error Resume Next
    Set wbk = Workbooks.Open(filename)
    On Error GoTo 0
    If wbk Is Nothing Then
        dtfile = dtfile - 1
        filename = "G:\Administration\Review\" & myfile & Format(dtfile, "mmddyyyy") & ".xlsx"
    End If
Loop

 

[Legacy 456155]

My code is dyslexic.

 

[Nichole09]

Thank you everyone for the help!!! GWteB that did it!!! It pulls up the latest saved file. Thank you!!

 

[GWteB]

You are welcome & thanks for letting us know.