asked on

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

Saurabh Singh Teotia

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...

Cook09

ASKER

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

Saurabh Singh Teotia

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...

Cook09

ASKER

Saurabh,

Yes...

Cook09

Saurabh Singh Teotia

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...

Cook09

ASKER

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

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cook09

ASKER

Thanks