Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    validating a filename (2003)

    Hi everyone,

    If LCase(strDocName) Like "[0-9][0-9][0-9][0-9][0-9][0-9]." & strExt Then
    GoTo CopyMerge

    I am using the above code to validate that the file has a 6 digit number then a valid extension. But I want to change it to look for a 6 digit filename where the first 6 charactors of the filename are digits.

    For example

    012456 - Mr Blooby 010508.doc The bit I need to validate is that the first 6 are infact numbers the last six are a date which I need to ignore.

    Example 2

    012456 - 010508.doc Again only the first 6 are important

    Example 3

    Mr Blobby 012456 Mr Jones.doc In this example I need to pull the 6 numbers in the middle

    I would like help with 3 pieces of code for each so I can figure out how and when to use in my code using If commands.

    Many thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: validating a filename (2003)

    Hi Kevin,

    Here's a test you can run to deal with all 4 scenarios:
    Sub Test()
    Dim strDocName As String
    Dim strExt As String
    Dim i As Integer
    Dim j As Integer
    j = 255
    strDocName = InputBox("Doc Name?")
    strExt = ".doc"
    strDocName = strDocName & strExt
    If strDocName Like "* ###### *" & strExt Then
    For i = 0 To 9
    If InStr(strDocName, i) < j And InStr(strDocName, i) > 0 Then j = InStr(strDocName, i)
    Next
    MsgBox Mid(strDocName, j, 6)
    ElseIf strDocName Like "###### *" & strExt Or strDocName Like "######" & strExt Then
    MsgBox Left(strDocName, 6)
    Else
    MsgBox "Invalid Filename"
    End If
    End Sub

    The message boxes simply output the 6-digit strings or the error message. You may or may not need the code to extract those, so modify as needed.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validating a filename (2003)

    Very long piece of code (more than 6,000 characters) moved to attachment by HansV

    Thanks for the reply, I am alittle confused as to the use of the input box.

    My script is used to move files from many locations, into one central location I select the source and target directories at the beginning of the script.

    I need it to look at the current source file and check if it has a 6digit number in a block and extract that as strNewFileName for use as the new file name...

    The below works for 012345 barry.doc
    and 012345.doc

    but not for barry 012345 jones.doc

    It recognises the right If command but I dont know how to extract the 6 digits in the middle of Letters, as each file has different words, I cant use for example Left(strSource, 6) as the next file may have more or less letters before the 6 digits.

    Thanks

    If LCase(strDocName) Like "[0-9][0-9][0-9][0-9][0-9][0-9]." & strExt Then
    GoTo CopyMerge
    Else
    End If

    If LCase(strDocName) Like "[0-9][0-9][0-9][0-9][0-9][0-9]*." _
    & strExt Then
    GoTo CopyMerge1
    Else
    End If

    ' If LCase(strDocName) Like "*[0-9][0-9][0-9][0-9][0-9][0-9]*." _
    ' & strExt Then
    ' GoTo CopyMerge2
    ' Else
    FileCopy strSource(intLoop2) & strDocName, strDump & strDocName
    Kill (strSource(intLoop2) & strDocName)
    ' End If
    Attached Files Attached Files
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: validating a filename (2003)

    This is not the fastest way to solve this problem, but it works. The function looks at consecutive six character strings until it finds a number to return or returns a blank string. It ignores leading spaces, periods, and commas that might be considered legal in a number but which do not fit your requirements. Further tweaking might be required!

    <code>Function getFirst6Numerals(aString As String) As String
    Dim intStart As Integer, strTest As String
    getFirst6Numerals = vbNullString
    If Len(aString) < 6 Then Exit Function
    For intStart = 1 To Len(aString) - 6
    strTest = Mid(aString, intStart, 6)
    If IsNumeric(strTest) Then
    If InStr(1, strTest, " ") = 0 And _
    InStr(1, strTest, ".") = 0 And _
    InStr(1, strTest, ",") = 0 Then
    getFirst6Numerals = strTest
    Exit For
    End If
    End If
    Next
    End Function

    Sub Test_getFirst6Numerals()
    ' To test, select a sample file name in a document and run this procedure
    Dim strFirstSix As String
    strFirstSix = getFirst6Numerals(Selection.Text)
    If strFirstSix <> vbNullString Then
    MsgBox "First six numerals are: " & strFirstSix, vbInformation
    Else
    MsgBox "Did not find 6 consecutive numerals", vbExclamation
    End If
    End Sub</code>

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: validating a filename (2003)

    Hi Kevin,

    The code I posted was for demonstration purposes only. Here's how you could work it into your code, eliminating 'CopyMerge1' and 'CopyMerge2' in the process (note the slight re-working on 'CopyMerge'):

    'Validate the filename format
    Dim i As Integer
    Dim j As Integer
    If strDocName Like "* ###### *." & strExt Then
    j = 255
    'Extract the required 6 digits for the new filename
    For i = 0 To 9
    If InStr(strDocName, i) < j And InStr(strDocName, i) > 0 Then j = InStr(strDocName, i)
    Next
    strNewDocName = Mid(strDocName, j, 6) & "*." & strExt
    GoTo CopyMerge
    ElseIf strDocName Like "###### *." & strExt Then
    'Use the left 6 digits for the new filename
    strNewDocName = Left(strDocName, 6) & "*." & strExt
    GoTo CopyMerge
    ElseIf strDocName Like "######." & strExt Then
    'Retain the old filename
    strNewDocName = strDocName
    GoTo CopyMerge
    Else
    FileCopy strSource(intLoop2) & strDocName, strDump & strDocName
    Kill (strSource(intLoop2) & strDocName)
    End If
    Loop
    Next
    GoTo ExitHandler

    CopyMerge:
    ' Try to open matching target filename
    On Error Resume Next
    Set doc = Documents.Open(FileName:=strTarget & strSubFolder & "" & strNewDocName, _
    AddToRecentFiles:=False)
    If doc Is Nothing Then
    'No match - Copy
    FileCopy strSource(intLoop2) & strDocName, strTarget & strSubFolder & "" & strNewDocName
    Kill strSource(intLoop2) & strDocName
    GoTo SortBadFiles2
    Else
    GoTo ValidateDuplicate
    End If
    ValidateDuplicate:

    Basically, the simplified code above replaces all of what you've now got from the line ''Validate the filename format' to the line 'ValidateDuplicate:'

    I believe Hans has answered your other question about checking for duplicates.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: validating a filename (2003)

    Thank you a new day and I can understand the code better now, many thanks.

    Kevin
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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