none
How to reject a file import if the name isn't in sequential order RRS feed

  • Question

  • have yearly data files (text delimited) that will each be named "2016 latest", "2017 latest", "2018 latest", etc. I have a macro written that will bring up the file window and allow the user to chose the file and then automatically import it into the workbook. However, I want to include some error control that will reject the file and endsub if, for example, the last file uploaded was "2016 latest" and the file they try to upload is "2015 latest". I'm also trying to prevent years from being duplicated. How would I do this? I've attached the code I have so far.

    Selection.QueryTable.Refresh BackgroundQuery:=False
    Dim fName As String
    fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If fName = "False" Then
    Exit Sub
    Static lastNum As Long
    Dim newNum As Long
    newNum = CLng(Mid(fName, 1 + InStrRev(fName, ""), 4))
    If lastNum <> 0 And newNum <> lastNum + 1 Then
    MsgBox "Invalid Data Selection"
    End If
    Else
    
    Range("A2:H2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sales History").Select
    Range("Table1[[#Headers],[Date]]").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Sheets("Main Menu").Select
    Range("A2:F2").Select
    Sheets("Annual Sales Summary").Select
    ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel, True
    Range("A2").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
    Sheets("Main Menu").Select
    Range("A2:F2").Select
    End Sub

    Monday, April 24, 2017 3:45 AM

All replies

  • For example
    Sub AAA()
        Dim fName       As Variant
        Dim vTmp        As Variant
        Dim newNum      As Long
        Dim vNum        As Variant
        Dim IsOK        As Boolean
    
        Static lastNum  As Long
        Static sYears   As String
    
        fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    
        If TypeName(fName) = "Boolean" Then
            Exit Sub
        End If
    
        vTmp = Split(fName, "\")
    
        If Not LCase(vTmp(UBound(vTmp))) Like "#### latest.txt" Then
            MsgBox "Wrong file name", vbExclamation
            Exit Sub
        End If
    
        newNum = CLng(Val(vTmp(UBound(vTmp))))
    
        If lastNum = 0 Then
            'first time
            IsOK = True
            sYears = CStr(newNum)
            lastNum = newNum
        Else
            'next time
    
            'Search for duplicates
            For Each vNum In Split(sYears, ",")
                If vNum = CStr(newNum) Then
                    MsgBox "Invalid Data Selection" & vbLf & "(It's a duplicate)"
                    Exit Sub
                End If
            Next vNum
    
            'is next year
            If newNum = lastNum + 1 Then
                IsOK = True
                lastNum = newNum
                sYears = sYears & "," & newNum
            Else
                IsOK = False
            End If
        End If
    
        If Not IsOK Then
            MsgBox "Invalid Data Selection" & vbLf & _
                   "You should choose " & lastNum + 1 & " year"
        Else
            MsgBox "OK. You can work", vbInformation
        End If
    
    End Sub

    Artik
    Tuesday, May 2, 2017 10:59 PM