Excel vba file renaming based on hyperlinks and status
AutoSkill: Experience-Driven Lifelong Learning via Skill Self-Evolution
npx -y skills add ECNU-ICALK/AutoSkill --skill excel-vba-file-renaming-based-on-hyperlinks-and-statusAssembled from the repository path, not quoted from the project. Check it against their README if it does not work.
One thing to look at
- no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.
What its author says it does
Copied from the file, not written here
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.
SKILL.md
2.1 KB, as published. Nobody here has run it
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
Dirfunction. - File Lock Handling: Check if the file is currently open (e.g., using
FreeFileand error handling) to prevent runtime errors. If open, close it before renaming. - Syntax: Use standard VBA
Namestatement for renaming operations. Avoid usingVBA.FileSystemmethods 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