Open file that has A Hash (#) Sign in the File Name

[samahiji]

Hi
I just noticed that Excel hyperlink won't be able to open a file that has pound sign (Hash #) in the file name.
One solution is to rename those files. However, I have so many files with (#) in their name which not possible solution.

Any thought on how to solve this issue?

 

[Micron]

Research & find code that loops through folders and renames the files by removing the octothorpe (#)?
Or use some other method that uses regular vba or Microsoft Scripting Runtime.

 

[John_w]

What type of hyperlinks are they? Created with the HYPERLINK function or inserted via the Insert Link dialogue?

There is a trick using the FollowHyperlink event, but this is fired only for the second type of hyperlink, and only after Excel has attempted to open the file. The trick - Disable Hyperlink From Opening Until Confirmed - OzGrid Free Excel/VBA Help Forum - requires that the 'Text to Display' of each hyperlink is the full file name of the hyperlinked file and the hyperlink is changed from 'Existing File or Web Page' to 'A Place in This Document' and refer to the cell of the hyperlink itself and use the ScreenTip property as the file reference. With this, Excel thinks it is an internal hyperlink and we can use the FollowHyperlink event to intercept the click, extract the file reference and open the file. Hyperlinks using the HYPERLINK function must be changed to the Insert Link method and then the same trick can be used.