thandel
asked on
Deleting a hyperlinks file
I have a hyperlink with a cell that points to a file on my local computer. How can I delete the file that the hyperlinks points to?
Thank you.
Thank you.
ASKER
Well I wanted to write a macro hence the reason I am asking the question.
I would like the macro to delete the file based on the active cell's link.
Sorry I thought I was clear that a macro was needed and File Deletion 101 wasn't preferred.
I would like the macro to delete the file based on the active cell's link.
Sorry I thought I was clear that a macro was needed and File Deletion 101 wasn't preferred.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No worries...
Is it possible to use something like kill ActiveCell.Hyperlinks(1).A
Is it possible to use something like kill ActiveCell.Hyperlinks(1).A
ASKER
Nevermind that worked. Thanks!
Sure, but put some error handling in case there is no hyperlink.
On Error Resume Next
Kill ActiveCell.Hyperlinks(1).A
On Error GoTo 0
Kevin
On Error Resume Next
Kill ActiveCell.Hyperlinks(1).A
On Error GoTo 0
Kevin
ASKER
Opps reacting too quick... when I try to run it I'm getting the following error:
Run time error '53' file not found.
However when I select the link the file opens, so the link is present and the file is present. Any ideas?
Run time error '53' file not found.
However when I select the link the file opens, so the link is present and the file is present. Any ideas?
I want to ask if the link really points to a file but that would be a pretty stupid question at this point :-)
Just for fun, run this code and post what the message box says. Also post the full path to the file. Maybe there is some default/current folder stuff going on...
MsgBox ActiveCell.Hyperlinks(1).A
Kevin
Just for fun, run this code and post what the message box says. Also post the full path to the file. Maybe there is some default/current folder stuff going on...
MsgBox ActiveCell.Hyperlinks(1).A
Kevin
ASKER
../../Desktop/BETA/beta.tx
I can't make my tests fail. Both the Kill and the follow hyperlink functions use the workbook's directory to locate the file. In other words, no matter what I do I can't make one fail and the other not.
What version of Excel are you using?
Kevin
What version of Excel are you using?
Kevin
ASKER
Excell 2003. Excel is adding the ../../ since the file I"ve selected is not in the same folder as the spreadsheet. If I place the file in the same folder and use:
Kill ActiveWorkbook.Path & "\" & ActiveCell.Hyperlinks(1).A
Kill ActiveWorkbook.Path & "\" & ActiveCell.Hyperlinks(1).A
Oops...my test was bad...
This should work:
ChDir ActiveWorkbook.Path
On Error Resume Next
Kill ActiveCell.Hyperlinks(1).A
On Error GoTo 0
Turns out Kill uses the current directory while the follow hyperlink function uses the workbook's directory.
Kevin
This should work:
ChDir ActiveWorkbook.Path
On Error Resume Next
Kill ActiveCell.Hyperlinks(1).A
On Error GoTo 0
Turns out Kill uses the current directory while the follow hyperlink function uses the workbook's directory.
Kevin
ASKER
The above will not work as the file I'm trying to delete is NOT in the same directory as the workbook. (or the current directory)
Right. But the follow hyperlink works from the workbook's directory. So if the follow hyperlink function can find it then the above code will work. Take your original example:
../../Desktop/BETA/beta.tx
Assuming the workbook is two levels deeper than Desktop then, by setting the current directory to the workbook's folder, Kill will move up two directories and then down to BETA to find beta.txt.
Remember we are trying to emulate the same path scheme that the follow hyperlinks function uses.
Kevin
../../Desktop/BETA/beta.tx
Assuming the workbook is two levels deeper than Desktop then, by setting the current directory to the workbook's folder, Kill will move up two directories and then down to BETA to find beta.txt.
Remember we are trying to emulate the same path scheme that the follow hyperlinks function uses.
Kevin
ASKER
Works! Thanks.... you are awesome at this stuff.
Kevin