Results 1 to 4 of 4

Thread: Adding a Message Window if files are not found in folder

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding a Message Window if files are not found in folder

    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

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding a Message Window if files are not found in folder

    Quote Originally Posted by jconkl02 View Post
    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 by Steve_; Aug 1st, 2019 at 02:41 PM.

  3. #3
    New Member
    Join Date
    May 2016
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding a Message Window if files are not found in folder

    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.

    Quote Originally Posted by Steve_ View Post
    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

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding a Message Window if files are not found in folder

    Quote Originally Posted by jconkl02 View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •