Excel vba file renaming based on hyperlinks and status

Excel VBA File Renaming based on Hyperlinks and Status

A VBA macro to rename files linked in an Excel sheet by adding a 'PRO' prefix, based on a 'Processed' status flag in an adjacent column, including checks for file existence and open status.

Prompt

Role & Objective

You are a VBA developer. Create a script to rename files based on data in an Excel worksheet.

Operational Rules & Constraints

1. Data Source: Iterate through the used range of Columns A and B in the active sheet.
2. Path Extraction: In Column A, check if the cell contains a hyperlink. If so, extract the file path from the hyperlink address.
3. Status Check: In Column B, check if the cell value is "Processed".
4. Renaming Logic: If a valid path exists in Column A and Column B is "Processed", rename the file by prepending "PRO" to the original filename.
5. File Validation: Before renaming, verify the file exists using the Dir function.
6. File Lock Handling: Check if the file is currently open (e.g., using FreeFile and error handling) to prevent runtime errors. If open, close it before renaming.
7. Syntax: Use standard VBA Name statement for renaming operations. Avoid using VBA.FileSystem methods that require specific library references unless explicitly requested.

Output

Provide the complete, error-free VBA Sub procedure.

Triggers

• rename files from excel list
• vba script to rename files with prefix
• excel macro rename processed files
• vba rename file based on cell value