Copying and renaming file - spaces issue

[melodramatic]

I got an assist on my macro yesterday, but have now come up with another issue that I don't know how to handle.

I am running a macro where I have a temp folder where I dump all document control files I receive. When I run the macro, all of the files disburse to their proper file folder, per project number (which are the first 7 letters of the document name). Makes document control filing easy.

I now need to not only move the files from the temp folder, but before doing so, I have to copy the file to a send folder. But, the catch is I have to copy the files so that they ONLY contain the document control number, and not any additional text the project manager may have attached to it before sending it out.

The earlier assist I got was to get the filename thru the "Rev#", and copy the file to that name.

However, my problem is that my Project Managers are playing games with the filenames, not always following the rule that has been set for them. Our document control #s are the 7-digit project number, dash, 2 or 3 letter practice, dash, 2 or 3 letter type of document, dash, 2 or 3 digit #, underscore, Rev#. A typical filename might look like the top line below, but we may have PMs write it in many different ways:

1234567-AB-CDE-89_Rev0 the way it SHOULD be
1234567-AB-CDE-89 Rev 0
1234567-AB-CDE-89 (Rev0)

Anyway, you get the idea. Thankfully, the guys don't foul it up until they get to the Revision.

What would work best for me is if I could copy the file with no spaces or parenthesis. From there, it would be very easy to grab the document control # by using "Rev" as the find spot for ending it.

I currently have the following written:

'MOVE FILES FROM TEMP DC ARCHIVE FOLDER TO PERM DC ARCHIVE
          
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objMyFolder = objFSO.GetFolder(TempFolder)
    PN_Missing_Count = 0
    
    For Each objMyFile In objMyFolder.Files
         
            FinalFolder = "X:\DOCUMENT CONTROL\" & Left(objMyFile.Name, 5) & "00 DOCUMENT CONTROL\" & Left(objMyFile.Name, 6) & "0 PROJECTS\" & Left(objMyFile.Name, 7)
    
            If Len(Dir(FinalFolder, vbDirectory)) = 0 Then
                PN_Missing_Count = PN_Missing_Count + 1
                PN_Missing_List = "List Missing" & vbNewLine & "Left(objMyFile.Name,7)"
            Else
                FileCopy TempFolder & "\" & objMyFile.Name, SendFolder & "\" & Left(objMyFile.Name, InStr(objMyFile.Name, "Rev") - 2) & "_Rev" & _
                        Right(Left(objMyFile.Name, InStr(objMyFile.Name, "Rev") + 3), 1) & ".pdf" 'WORKING XXX
                FileCopy TempFolder & "\" & objMyFile.Name, FinalFolder & "\" & objMyFile.Name
                Kill TempFolder & "\" & objMyFile.Name
            End If
             
    Next objMyFile

The problem here is that I'm getting some items with no Rev# (they followed "Rev" with a space), and extra spaces before the underscore in some instances. How can I fix this?

Thanks for your help!

 

[rlv01]

The problem here is that I'm getting some items with no Rev# (they followed "Rev" with a space), and extra spaces before the underscore in some instances. How can I fix this?

Thanks for your help!

Once you have to start predicting all the ways a user might type something, things get messier. A UDF to clean up the name might be the way to go. That will make it easier to adapt to any new variations that might surface. One example:

Function NameCleanUp(fname As String) As String
    Dim S As String
    Dim SPos As Long

    S = Replace(Application.Trim(fname), "(", "")
    S = Replace(S, ")", "")
    S = Replace(S, " Rev", "Rev")
    S = Replace(S, "Rev", "_Rev")
    S = Replace(S, "__Rev", "_Rev")
    S = Replace(S, "Rev ", "Rev")

    SPos = InStr(S, " ")

    If SPos > 0 Then
        S = Left(S, SPos)
    End If

    NameCleanUp = Trim(S)
End Function

Sub UDFTest()

    Dim ID As String

    ID = "1234567-AB-CDE-89_Rev0 the way it SHOULD be"
    MsgBox NameCleanUp(ID)
    '
    ID = "1234567-AB-CDE-89 Rev 12 Aaron wrote a description here"
    MsgBox NameCleanUp(ID)

    ID = "1234567-AB-CDE-89 (Rev  11) Aaron wrote a description here"
    MsgBox NameCleanUp(ID)

    ID = "1234567-AB-CDE-89_Rev1"
    MsgBox NameCleanUp(ID)

End Sub

 

[melodramatic]

LOL - I was just about to put in that I had solved it using the replace - and you beat me to it! Basically, I followed exactly what you said - created a string, and then replaced spaces, parens and underscores. I then created the new filename using the string UP TO "Rev", added in my own "_", added the "Rev#", then added ".pdf". However, I like your way of doing the trim, and think I'll switch mine around to match yours there.

Thank you so much for helping me out with confirming this is the best route!