| id | 46e6c38e-0fc9-407a-bd6d-88d7aa4d90f3 |
| name | Excel VBA File Renaming based on Hyperlinks and Status |
| description | 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. |
| version | 0.1.0 |
| tags | ["vba","excel","file-rename","automation","macro"] |
| 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"] |
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
- Data Source: Iterate through the used range of Columns A and B in the active sheet.
- Path Extraction: In Column A, check if the cell contains a hyperlink. If so, extract the file path from the hyperlink address.
- Status Check: In Column B, check if the cell value is "Processed".
- 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.
- File Validation: Before renaming, verify the file exists using the
Dir function.
- 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.
- 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
