locked
VBA Error control macro- restrict file imports based on file name RRS feed

  • Question

  • I'm trying to say something to the effect of "don't import this file if xxxx (the first 4 digits of the imported file name) is less than or equal to yyyy (the first four digits of the most recently imported file name

    Files must be imported into Excel sequentially, and no duplicates are allowed. "2016 recent" cannot be imported after "2017 recent.

    I am using Excel 2016. Here is the code I have so far. I have VERY limited experience with VBA and am not even sure if any of this is right. Any help greatly appreciated. 

     Range("A1:H439").Select
        Selection.QueryTable.Refresh BackgroundQuery:=False
        Dim fName As String
        fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
        If fName = "False" Then
        Exit Sub
        End If
        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
        If newNum = lastNum + 1 Then
        Selection.Copy
        Sheets("Sales History").Select
        Range("A21").Select
        Selection.End(xlDown).Select
        Range("A993").Select
        ActiveSheet.Paste
        Range("K992").Select
        Selection.AutoFill Destination:=Range("K992:K1431")
        Range("K992:K1431").Select
        End If
    End Sub

    Thursday, April 20, 2017 6:14 PM

All replies

  • Hello,

    Because your code is incomplete I am guessing here but it appears your are trying to determine if the numeric value of the first 4 characters of the selected file, fName, is less than a previously imported file name.

    You pass a value to the variable newNum...

    newNum = CLng(Mid(fName, 1 + InStrRev(fName, ""), 4))

    ...but I do not see where you pass a value to the variable lastNum.

    When you populate fName using the GetOpenFileName the value you receive will be a full path and file name...

     fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")


    I believe you need to seperate the file name from the full path and file name.  From there you can determine if the value of the first 4 characters is equal to lastNum +1.

    Here are some functions that will allow you to:

    Seperate the path, file name with extension, file name without extension and just the extension.

    Function FileNameFromPath(strFullPath As String) As String
        '---------------------------------------------------------------------------------------
        ' Procedure : FileNameFromPath
        ' Author    : Kevin Waddle, kmwaddle@gmail.com
        ' Purpose   : Returns File Name WITH Extenstion from full path and file name
        '               FileNameFromPath("C:\Users\Waddle\Desktop\Test.html")
        '               Returns: Test.html
        '---------------------------------------------------------------------------------------
        '
        FileNameFromPath = Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath, "\"))
    End Function
    
    Function FileNameNoExtensionFromPath(strFullPath As String) As String
        '---------------------------------------------------------------------------------------
        ' Procedure : FileNameNoExtensionFromPath
        ' Author    : Kevin Waddle, kmwaddle@gmail.com
        ' Purpose   : Returns File Name WITHOUT Extenstion from full path and file name
        '               FileNameNoExtensionFromPath("C:\Users\Waddle\Desktop\Test.html")
        '               Returns: Test
        '---------------------------------------------------------------------------------------
        '
        Dim intStartLoc                     As Integer
        Dim intEndLoc                       As Integer
        Dim intLength                       As Integer
    
        intStartLoc = Len(strFullPath) - (Len(strFullPath) - InStrRev(strFullPath, "\") - 1)
        intEndLoc = Len(strFullPath) - (Len(strFullPath) - InStrRev(strFullPath, "."))
        intLength = intEndLoc - intStartLoc
    
        FileNameNoExtensionFromPath = Mid(strFullPath, intStartLoc, intLength)
    End Function
    
    Function FileExtensionFromPath(ByRef strFullPath As String) As String
        '---------------------------------------------------------------------------------------
        ' Procedure : FileExtensionFromPath
        ' Author    : Kevin Waddle, kmwaddle@gmail.com
        ' Purpose   : Returns File Extension from full path and file name
        '               FileExtensionFromPath("C:\Users\Waddle\Desktop\Test.html")
        '               Returns: html
        '---------------------------------------------------------------------------------------
        '
        FileExtensionFromPath = Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath, "."))
    End Function
    
    Function FolderFromPath(ByRef strFullPath As String) As String
        '---------------------------------------------------------------------------------------
        ' Procedure : FolderFromPath
        ' Author    : Kevin Waddle, kmwaddle@gmail.com
        ' Purpose   : Returns Path from full path and file name
        '               FolderFromPath("C:\Users\Waddle\Desktop\Test.html")
        '               Returns: C:\Users\Waddle\Desktop\
        '---------------------------------------------------------------------------------------
        '
        FolderFromPath = Left(strFullPath, InStrRev(strFullPath, "\"))
    End Function
    
    

    From here you can use something like...

    fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    strNewFile = FileExtensionFromPath(fName)
    newNum = CLng(Left(strNewFile, 4))
    

    Hope this Helps,

    Kevin

    Sunday, April 23, 2017 4:04 PM