Adding a Message Window if files are not found in folder

jconkl02

Board Regular
Joined
May 25, 2016
Messages
55
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.
Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.




Code:
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
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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
Back
Top