Adding a Message Window if files are not found in folder

jconkl02

New Member
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
 

Steve_

Board Regular
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:

jconkl02

New Member
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
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top