Add Prefix to Excel or Word documents in a Subdirectory

Given a subdirectory that has multiple Excel and Word documents, there is a need to  rename each file with a particular prefix....which needs to be entered into a dialogue box prior to the renaming process.  The prefix would apply to all the files within a particular subdirectory.

Example:
Invoice 01.xlsx                       -->   FIN-Invoice 01.xlsx     or....
Quotation - Cust. 015.doc    -->  SALES-Quotation - Cust. 015.doc  

Thanks,
Cook09
Cook09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Cook09,

Quick question on this..In this sub-directory..Do you have sub-folders as well in it which has these excel or word files in it? Or it doesn't have any sub-folders in it??

Saurabh...
0
Cook09Author Commented:
Saurabh,

Within a given Start Path, there will be sub-folders, that may need different prefixes. So, the code should only work with a singular folder.

Is it also possible that if a given file is a .xls or .doc, that it can also be saved with the newer extensions?

Cook09
0
Saurabh Singh TeotiaCommented:
Cook09,

When you say you want to convert?? You want to change them to xlsx or docx..this is what you want to do?

Saurabh...
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Cook09Author Commented:
Saurabh,

Yes...

Cook09
0
Saurabh Singh TeotiaCommented:
Cook09,

Their you go..Use this code...

Sub renamefiles()
    Dim file As Variant, fcheck As String
    Dim fname As String
    Dim xpath As String, str As String

    xpath = "your path here"
    If Right(xpath, 1) <> "\" Then xpath = xpath & "\"
    file = Dir(xpath)
    Do Until file = ""
        If InStr(1, file, ".xls", vbTextCompare) > 0 Or InStr(1, file, ".doc", vbTextCompare) > 0 Then
            If Right(file, 4) = ".xls" Then
                fcheck = ".xlsx"
            ElseIf Right(file, 4) = ".doc" Then
                fcheck = ".docx"
            Else
                fcheck = Right(file, Len(file) - InStrRev(file, ".", -1, vbTextCompare) + 1)
            End If

            If fcheck = ".xlsx" Then
                str = "FIN-"
            Else
                str = "SALES-"
            End If

            fname = str & Left(file, InStrRev(file, ".", -1, vbTextCompare) - 1)

            Name xpath & file As xpath & fname & fcheck

        End If



        file = Dir
    Loop

End Sub

Open in new window


Saurabh...
0
Cook09Author Commented:
Saurabh,
The code may need to open then close a file if the extension needs changing.  Right now it will change the extension, but when I try to open it, an error appears, "...the file format or file extension is not valid...."

Cook09
0
Saurabh Singh TeotiaCommented:
Cook09,

Yeah i realize that when i open my files too..Now for that you need to write a complete second macro..for which can you open another question as that's different then the task what you originally asked for..

Also enclosed is the revised macro which will just convert .xlsx or .xlsm or .docx file only..

Sub renamefiles()
    Dim file As Variant, fcheck As String
    Dim fname As String
    Dim xpath As String, str As String

    xpath = "your path here"
    If Right(xpath, 1) <> "\" Then xpath = xpath & "\"
    file = Dir(xpath)
    Do Until file = ""
        If InStr(1, file, ".xlsx", vbTextCompare) > 0 Or InStr(1, file, ".docx", vbTextCompare) > 0 Or InStr(1, file, ".xlsm", vbTextCompare) > 0 Then
          
                fcheck = Right(file, Len(file) - InStrRev(file, ".", -1, vbTextCompare) + 1)
          

            If fcheck = ".xlsx" Or fcheck = ".xlsm" Then
                str = "FIN-"
            Else
                str = "SALES-"
            End If

            fname = str & Left(file, InStrRev(file, ".", -1, vbTextCompare) - 1)

            Name xpath & file As xpath & fname & fcheck

        End If



        file = Dir
    Loop

End Sub

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cook09Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.