Hello all!

I have set up a function that loops through all the files in a certain
folder and find & open a prior month's file and return that file name.
However, when I run the function sometimes (not all the time) I get a
run-time error 1004, file not found. I don't understand why this is since
the loop is only running through the file names in that folder. The file to
be opened is not in use by another user either. My code is below. Any
insight would be amazing. Thanks.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

Sub test()
Const acct As String = "xxx"
Dim tmp As String

tmp = PrevFile(acct)
End Sub

Function PrevFile(acct As String)
Dim myPath As String, myName As String, folder As String
Dim m As Variant
Dim y As Integer
Dim tmp As Variant

m = Month(ThisWorkbook.Worksheets(1).Range("B3").Value ) - 1
If m < 10 Then
m = "0" & m & "_"
Else
m = m & "_"
End If
y = Year(ThisWorkbook.Worksheets(1).Range("B3").Value)
folder = y & " PC Reports"
myPath = "G:\Reports New\" & folder & "\*.xls"
myName = Dir(myPath)
Do
If InStr(1, myName, m, vbTextCompare) 0 And InStr(1, myName, acct,
vbTextCompare) 0 Then
Application.DisplayAlerts = False
Workbooks.Open Filename:=myName, UpdateLinks:=False
Application.DisplayAlerts = True
Exit Do
End If
myName = Dir
MsgBox myName
Loop While myName < ""
PrevFile = myName
End Function

myName = Dir(myPath)
myName will hold the filename--it doesn't include the drive or path.

Maybe:

Workbooks.Open Filename:="G:\Reports New\" & myName, UpdateLinks:=False

(or create a variable for just the path and use that.)

Michelle wrote:

Hello all!

I have set up a function that loops through all the files in a certain
folder and find & open a prior month's file and return that file name.
However, when I run the function sometimes (not all the time) I get a
run-time error 1004, file not found. I don't understand why this is since
the loop is only running through the file names in that folder. The file to
be opened is not in use by another user either. My code is below. Any
insight would be amazing. Thanks.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

Sub test()
Const acct As String = "xxx"
Dim tmp As String

tmp = PrevFile(acct)
End Sub

Function PrevFile(acct As String)
Dim myPath As String, myName As String, folder As String
Dim m As Variant
Dim y As Integer
Dim tmp As Variant

m = Month(ThisWorkbook.Worksheets(1).Range("B3").Value ) - 1
If m < 10 Then
m = "0" & m & "_"
Else
m = m & "_"
End If
y = Year(ThisWorkbook.Worksheets(1).Range("B3").Value)
folder = y & " PC Reports"
myPath = "G:\Reports New\" & folder & "\*.xls"
myName = Dir(myPath)
Do
If InStr(1, myName, m, vbTextCompare) 0 And InStr(1, myName, acct,
vbTextCompare) 0 Then
Application.DisplayAlerts = False
Workbooks.Open Filename:=myName, UpdateLinks:=False
Application.DisplayAlerts = True
Exit Do
End If
myName = Dir
MsgBox myName
Loop While myName < ""
PrevFile = myName
End Function

--

Dave Peterson

Ah yes, forgot about that. I made the fix and it works as I expected it to.
Wonder why it worked sometimes then. My current directory must have been the
directory the file was located in.

Thanks for the answer. I felt like I was taking crazy pills or something!

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx


"Dave Peterson" wrote:

myName = Dir(myPath)
myName will hold the filename--it doesn't include the drive or path.

Maybe:

Workbooks.Open Filename:="G:\Reports New\" & myName, UpdateLinks:=False

(or create a variable for just the path and use that.)

Michelle wrote:

Hello all!

I have set up a function that loops through all the files in a certain
folder and find & open a prior month's file and return that file name.
However, when I run the function sometimes (not all the time) I get a
run-time error 1004, file not found. I don't understand why this is since
the loop is only running through the file names in that folder. The file to
be opened is not in use by another user either. My code is below. Any
insight would be amazing. Thanks.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

Sub test()
Const acct As String = "xxx"
Dim tmp As String

tmp = PrevFile(acct)
End Sub

Function PrevFile(acct As String)
Dim myPath As String, myName As String, folder As String
Dim m As Variant
Dim y As Integer
Dim tmp As Variant

m = Month(ThisWorkbook.Worksheets(1).Range("B3").Value ) - 1
If m < 10 Then
m = "0" & m & "_"
Else
m = m & "_"
End If
y = Year(ThisWorkbook.Worksheets(1).Range("B3").Value)
folder = y & " PC Reports"
myPath = "G:\Reports New\" & folder & "\*.xls"
myName = Dir(myPath)
Do
If InStr(1, myName, m, vbTextCompare) 0 And InStr(1, myName, acct,
vbTextCompare) 0 Then
Application.DisplayAlerts = False
Workbooks.Open Filename:=myName, UpdateLinks:=False
Application.DisplayAlerts = True
Exit Do
End If
myName = Dir
MsgBox myName
Loop While myName < ""
PrevFile = myName
End Function

--

Dave Peterson

If you were lucky, the current directory was the one you wanted to use. So it
would open fine.

If you were unlucky, it would fail on the first attempt to open the workbook
with that name. (Your current directory wasn't the one you wanted.)

If you were really unlucky, the wrong files in a wrong directory (with the same
filenames) were opened and processed. If you were really, really unlucky, then
there wouldn't have been a mismatch and the code would have ended nicely.



Michelle wrote:

Ah yes, forgot about that. I made the fix and it works as I expected it to.
Wonder why it worked sometimes then. My current directory must have been the
directory the file was located in.

Thanks for the answer. I felt like I was taking crazy pills or something!

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

"Dave Peterson" wrote:

myName = Dir(myPath)
myName will hold the filename--it doesn't include the drive or path.

Maybe:

Workbooks.Open Filename:="G:\Reports New\" & myName, UpdateLinks:=False

(or create a variable for just the path and use that.)

Michelle wrote:

Hello all!

I have set up a function that loops through all the files in a certain
folder and find & open a prior month's file and return that file name.
However, when I run the function sometimes (not all the time) I get a
run-time error 1004, file not found. I don't understand why this is since
the loop is only running through the file names in that folder. The file to
be opened is not in use by another user either. My code is below. Any
insight would be amazing. Thanks.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

Sub test()
Const acct As String = "xxx"
Dim tmp As String

tmp = PrevFile(acct)
End Sub

Function PrevFile(acct As String)
Dim myPath As String, myName As String, folder As String
Dim m As Variant
Dim y As Integer
Dim tmp As Variant

m = Month(ThisWorkbook.Worksheets(1).Range("B3").Value ) - 1
If m < 10 Then
m = "0" & m & "_"
Else
m = m & "_"
End If
y = Year(ThisWorkbook.Worksheets(1).Range("B3").Value)
folder = y & " PC Reports"
myPath = "G:\Reports New\" & folder & "\*.xls"
myName = Dir(myPath)
Do
If InStr(1, myName, m, vbTextCompare) 0 And InStr(1, myName, acct,
vbTextCompare) 0 Then
Application.DisplayAlerts = False
Workbooks.Open Filename:=myName, UpdateLinks:=False
Application.DisplayAlerts = True
Exit Do
End If
myName = Dir
MsgBox myName
Loop While myName < ""
PrevFile = myName
End Function

--

Dave Peterson


--

Dave Peterson