Macro giving me Runtime error '13': Type mismatch (caused by a comma in the file path?)

[Justin_time]

I found the following macro code online that seems to do what I am looking for, which is to present an Input Box to select multiple cells at once which contain hyperlinks to open other Excel documents, either locally on my computer, or located on a network share.

Please Login or Register  to view this content.

My local files are opened via the macro just fine, but none of the network files (using the UNC path to the file) are opening, but instead throwing the Run-time error '13': Type mismatch error.

I believe that the cause of the error is due to the fact that there is a comma in the network share path name. When I created a local file path, also with a comma in it, I am getting the same Runtime error for a file that would otherwise open for a path that does not contain a comma. (I do not have access to a network share that does not have at least one comma in the path name.) When I clicking on the links one at a time they will open the files just fine, but the macro, in its current state, cannot.

An generic example of the local hyperlink would be:
=HYPERLINK("C:\Users\Name\Desktop\Offline Data Files\Biller, Name\Client TEST - Biller\Billing\2021\Client Template.xlsx","Client Comma in Local File Path Test")

When I click on the debug button in the error message box, it highlights the following line in the code:
url = Evaluate(Left(c.Formula, InStr(1, c.Formula, ",") - 1) & ")")

Now I do not know anything about VBA coding, so I am not able to discern what exactly is causing the error in that line, but I think it might have something to do with the "," that comes after the second occurrence of "c.Formula".

My question is, is it possible to overcome this, or is it just not possible to open a file on a path that contains one or possible more commas in it using VBA? Thank you.

[Kenneth Hobson]

If you can not do it manually, a macro can not do it. Paste the url string into a web browser and the unc into File Explorer to verify that it works manually. If so, post string with the url or unc, obfuscated if needed).

If unc paths give an issue, you can always assign a virtual drive to avoid the problem.

[Justin_time]

Hi Kenneth,
I am sorry if I did not make it clear that when I manually click on the hyperlinks that I have created, either local to my PC, or located on the network share, they do work. The hyperlink opens the Excel file(s) I linked to. What does not work is if there is a comma in the file path. The hyperlinks work with the commas in the file path when manually clicking on them, just not when I try running the macro against hem.

[Kenneth Hobson]

Being html, some characters you need to escape. For comma, use &#44.

Space characters may need replacing as well.

A simple Replace() or two may suffice. If more help is needed, post back.

[Justin_time]

Hi Kenneth,
Yes, I need more help.
I am going to upload am xlsm file with actual hyperlinks I have created to local files on my PC for an example, to see if that can help clarify what I am asking for help with.

I appreciate the rapid responses to my questions. However it seems that what I thought would be something fairly simple to answer is getting more complicated than my understanding can follow.

I do not believe that I am having issues with spaces in the file names, but the commas are definitely causing an issue. It does not matter if there is a comma in the files name,
like "test file, Excel.xlsx" instead of "test file excel.xlsx" or if there is a comma in the file path name.

Html is not something I have even a beginning of understanding about, so I am completely in the dark about what an escape character is, or how to replace a "," (comma) with "&#44".

Thank you.

[Kenneth Hobson]

No time to look at your file tonight. See if the attached file helps...

[jindon]

See if this works

Please Login or Register  to view this content.

[Kenneth Hobson]

When parsing a hyperlink formula, I like to use the Split() method similar to jindon's. His code will solve your problem. When you compile it, it will show that you need to remove the duplicate "i as Long".

As you surmised, partly your issue is finding the first comma with InStr(). Even so, using Left would include the "=HYPERLINK(" string too. Why that worked for non-comma filenames I don't know. You can parse the url this way too:

Please Login or Register  to view this content.

[Justin_time]

Jindon,
Thank you for the modified code. I tried it and received a compilation error, as noted by Kenneth in his response #8 below. I removed the second occurrence of ", i As Long", and your code works to open files with a comma in the file path and or file name.

However, a new problem has come up when opening .xlsm files. I am not getting the Security Warning "macros have been disabled" prompt at the top of the screen to enable content, and therefore I am not able to work with the file after opening because the content I need to enable is not populated in the file. I do have "all macros disabled with warning" set in my Excel settings.

[Kenneth Hobson]

In Options Trust Center did you set lowest security and check the box to trust VBA?

[Justin_time]

Kenneth,
Thank you for your code as well. It works for files with commas in the file name or path. However, now like Jindon's code, when I open an .xlsm file I do not receive the Security Warning for disabled macros / content. If I cannot enable the content, the opened file is missing information / data that is needed for me to work with the file.

I also changed the settings under the options in trust center as you suggested, and while I no longer receive the security prompt when opening the files by double clicking on them in Explorer, or by manually clicking on the hyperlink, the data is still blocked when using Jindon's or your new code.

[jindon]

Oops, sorry about i.
Not sure if this works

Please Login or Register  to view this content.

[Kenneth Hobson]

It looks like you will want to set a trusted location. https://www.automateexcel.com/macros/enable-macros/