Adding a Message Window if files are not found in folder

[jconkl02]

I have macro that a couple of folks on this board wrote for me from a previous request. It works great, but after users started using it I found that some sort of check needs to be put into place. The way it is written it goes and looks in the users Downloads folder on their computer for two .xls files that begin with the word "report". One of the files will have the word "Program" in A1 and the other report will have "Number" in A1.

I need something added that will show a message box if the macro can not find a report*.xls in the Downloads folder.

Thanks in advance.

Sub LoadReportFromDownloadFolder()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wb As Workbook, fPath As String, fName As String, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("LastComment")
Set sh2 = Sheets("Tickets")

fPath = "C:\Users\me\Downloads\"
fName = Dir(fPath & "Report*.xls")

    Do While fName <> ""
        On Error Resume Next
        Set wb = Workbooks.Open(fPath & fName)


        If wb.Sheets(1).Range("A1") = "Program" Then
            If sh1.Range("A1") = "" Then
                wb.Sheets(1).UsedRange.Copy sh1.Range("A1")
            Else
                wb.Sheets(1).UsedRange.Copy sh1.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        ElseIf wb.Sheets(1).Range("A1") = "Number" Then
            If sh2.Range("A1") = "" Then
                wb.Sheets(1).UsedRange.Copy sh2.Range("A1")
            Else
                wb.Sheets(1).UsedRange.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        End If
        
    Dim wbFile As String
    wbFile = wb.FullName
    wb.Close False
    Kill wbFile
        fName = Dir
        Set wb = Nothing
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = Ture
End Sub

 

[Steve_]

I have macro that a couple of folks on this board wrote for me from a previous request. It works great, but after users started using it I found that some sort of check needs to be put into place. The way it is written it goes and looks in the users Downloads folder on their computer for two .xls files that begin with the word "report". One of the files will have the word "Program" in A1 and the other report will have "Number" in A1.

I need something added that will show a message box if the macro can not find a report*.xls in the Downloads folder.

Thanks in advance.

Sub Check_Exist()
    'Set your variables accordingly...
    'strFULLPATH = "C:\Users\Steve\Desktop\Test
    'strFILENAME = "REPORT_NAME.xlsx"
    If Dir(strFULLPATH & "\" & strFILENAME, vbDirectory) = vbNullString Then
         MsgBox "File not found!"
    End If
End Sub

 

[jconkl02]

Works perfect Steve_.

I was trying to make it work with the fPath and fName variables that I had set already with no luck. Thanks for the assist.

Sub Check_Exist()
    'Set your variables accordingly...
    'strFULLPATH = "C:\Users\Steve\Desktop\Test
    'strFILENAME = "REPORT_NAME.xlsx"
    If Dir(strFULLPATH & "\" & strFILENAME, vbDirectory) = vbNullString Then
         MsgBox "File not found!"
    End If
End Sub

 

[Steve_]

Works perfect Steve_.

I was trying to make it work with the fPath and fName variables that I had set already with no luck. Thanks for the assist.

Glad to help! Let us know if there is anything else.