Reading Filename from a Hyperlink in a cell and renaming the file it links to.

I am trying to create a spreadsheet to better deal with a report I get on a daily basis.

The report is generated on a website containing various infromation including a link to the XML file that has the error. The link is displayed as ">>>>" but is hyperlinked to a shared network resource. Due to errors in XML we have to fix these error and keep the original file.

Currently we can open the XML in notepad++ which is great, but before we can save any changes we need to rename the original file to keep a copy of the mistake. Since the file paths vary quite widely, and the folder the XML file is in can contain up to approxomatelly 1000 items manually locating the file to rename can be quite time consuming.

Is it possible to create a macro that will read the hyperlinked url and rename the file to "originalname.xml.old"?

Many thanks for you time looking into this.

My understanding is that you want to:
1. Find all cells in the Worksheet that contain ">>>>".
2. Each cell that contains ">>>>" contains a hyperlink.
3. You want the worksheet to remain untouched.
4. You want each hyperlink file in cells that contain ">>>>"
(such as "originalname.xml") to be renamed to "originalname.xml.old"

NOTE: I think you probably want "originalname.xml" to be copied to "originalname.xml.old".

The attached macro file:
1. Was created and tested using Excel 2003 on a Windows Vista 32 bit system.
2. Does all of the above, however you may want to keep "originalname.xml" and create a copy
called "originalname.xml.old" (The macro gives you the ability to choose one or the other
by commenting out the appropriate line.
3. Excel seems to store hyperlinks as relative paths by default. To use absolute
paths there is a setting in Excel 2003.
'File > Properties > Summary > Hyperlink Base > q: (Any rive not used)
Default is BLANK, which yields relative paths.
4. Most error checking has been omitted to make the example more readable. The
error checking in the code, prevents Excel RUNTIME ERRORS.

Please Login or Register  to view this content.