Macro - rename file with control # only

[melodramatic]

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.

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:

1234567-AB-CDE-89_Rev0 Aaron wrote a description here

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, 22) 'THIS IS THE NEW LINE I'M TRYING TO WORK OUT
                FileCopy TempFolder & "\" & objMyFile.Name, FinalFolder & "\" & objMyFile.Name
                Kill TempFolder & "\" & objMyFile.Name
            End If
             
    Next objMyFile

But, because of the 2 or 3 letter variations of the 2nd, 3rd and 4th sections, I cannot just come up with the left 22 digits of the filename.

Basically, I know how to run this in a calculation in an Excel cell, but I don't know how to put it in macro language. Can you help me convert this... = LEFT(D18,FIND("Rev",D18,1)+3) to VBA?

Thank you in advance!

 

[rlv01]

Basically, I know how to run this in a calculation in an Excel cell, but I don't know how to put it in macro language. Can you help me convert this... = LEFT(D18,FIND("Rev",D18,1)+3) to VBA?

Thank you in advance!

It should be something like this: Left(objMyFile.Name, InStr(objMyFile.Name, "Rev") + 3)

 

[melodramatic]

Thank you so much! I only needed to add the & ".pdf" (which my brain left out), and it worked perfectly.

Thank you, thank you, thank you!