Ignoring wildcard function of FollowHyperlink within a text string in VBA

Question

I am trying to open a file path in Explorer using the .FollowHyperlink method and get errors on the strings with the "#" character. How do I format the string to make .FollowHyperlink ignore the wildcard functionality? For instance how would I format the following file path:

G:\Building\#500 Main St.\Loans\

What are you trying to open? It helps to explain what you are doing because there is no such thing as a wildcard to a string. — HackSlash, Aug 03 '21 at 22:19
Hi @hackslash, I've copied the file path of a folder in a variable passed to the .followhyperlink() method in order to open the target folder in windows explorer. This works for 95% of the folder paths. The remaining 5% failures are all folder paths with the "#" character somewhere in the string. Removing the # removes the error. As # is a wildcard in excel, I assumed that I just need to "exit" that functionality as you can tell from my initial post. I know I can rename the folder names to remove the "#" but would like to understand how vba is handling #s in my string. — mttCCI, Aug 04 '21 at 21:09
If you are opening a folder in explorer then the answer from @Tim Williams below is correct. — HackSlash, Aug 04 '21 at 21:21
@hackslash, I agree the shell function solution below does work. However, I think is a little less desirable as the .followhyperlink() method will reuse explorer windows and doesn't result in several windows and dupe windows. I've fixed my problem (just removed the # from all folders) in this specific case, I'm am now hoping someone can help me understand why the .followhyperlink() method errors out with "#" in the text string. Thank you for your help though. — mttCCI, Aug 04 '21 at 21:32

score 1 · Answer 1 · answered Aug 03 '21 at 20:27

1

You can use Shell for this:

Shell "C:\WINDOWS\explorer.exe ""G:\Building\#500 Main St.\Loans\""", vbNormalFocus

answered Aug 03 '21 at 20:27

Tim Williams

154,628
8
97
125

score 0 · Accepted Answer · answered Aug 04 '21 at 21:41

We can tell by looking at the documentation that the first argument is expected to be an Address which must follow the rules of a valid URI as defined in the RFC.

The hash # is a reserved symbol in the URI RFC.

You would have to URL Encode any string you pass in to it to avoid reserved symbols. There is no built-in method to URL encode strings in Access, but there is in Excel.

You can see a full discussion of URL Encode in VBA here: How can I URL encode a string in Excel VBA?

Here is an example of your URL encoded local path:

FollowHyperlink("G%3A%5CBuilding%5C%23500%20Main%20St.%5CLoans%5C")

my newb programming brain is going to have to study this for quite a while, but what a great answer! Thanks :) — mttCCI, Aug 04 '21 at 21:49

Ignoring wildcard function of FollowHyperlink within a text string in VBA

2 Answers2