Hyperlink Function with Blanks in Path

[FracinDean]

Dear Mr Excel members,

After searching this forum and looking at many examples, I'm still struggling to figure out how to use path or file or worksheet names that have blanks in them when using the Excel Hyperlink function.

The following code is working when none of the ranges named "myPath", "myFile", and "mySheet" have blanks in their values, but a blank in any of them causes errors. I have tried various combinations of single quotes around "mySheet", and multiple consecutive double quotes before myPath and after myFile but so far have not been able to get the right combination to make it work when there are blank spaces in the range values.

=HYPERLINK(myPath&"\"&myFile&"\"&mySheet&"!"&myTarget","Display Name")

Can you show me how to generalize this function call to accommodate blank spaces in the path, file, or sheet names?

 

[6StringJazzer]

I cannot get this to work even without blanks. Can you give an example of the values you are using that work? I can only get it to work with a file path, not with a sheet and cell reference.

 

[FracinDean]

It's really just the path and file name I'm interested in. For my application I can hard code in the sheet name and the cell reference, which is what I was doing. The following is like what I was doing successfully as long as there were no blanks in the path and file name:

=HYPERLINK(myPath&"\"&myFile&"\Sheet1!$a$1","Display Name")

Sorry I muddled things up.

 

[6StringJazzer]

The reason I am asking for concrete detail, including an example of an actual path, is that this works for me. You will note that there are spaces in both the path and the file name.
=HYPERLINK("C:\Users\Jeff\Dropbox\MS Office Tools\Excel\Excel Forum\Excel Dropbox Scratch\adokskel=Before test.xlsx","test")

There is no case of adding a sheet name and cell reference that works, regardless of spaces. I just get an error that says "Cannot open the specified file." That is why I can't figure out how it's possible that "The following code is working when none of the ranges...have blanks in their values"

 

[FracinDean]

The following code is working in my workbook:

=HYPERLINK(currPath&"\"&currFile&"\Results!sheetHome","RESULTS")

currPath is the range name for my cell "F1" on my "About" worksheet.
The contents of cell F1 are like this, except with no double quotes in the cell: "C:\Users\wxyz\Desktop\XYZ_123456".

currFile is the range name for my cell "F2" on the "About" sheet.
The contents of cell F2 are like this, except with no double quotes in the cell:
"WXYZ_v350_20200306_Match.xlsm".

The hyperlink code above is in cell B34 of the about sheet.

When I click on the link in cell B34, it takes me to the cell with a range name of "sheetHome" in the "Results" worksheet of the file named by the path and file name. When I remove one of the underscores from either the file path or the file name and also change the path or file name to match, I get the following useless error dialogue box: "An unexpected error has occurred."

I think the only reason this is even working is because the path and file name are the current path.

What I'm trying to do is set up hyperlinks back and forth to different locations within the workbook, no matter where it is saved or what the new file name is. This is easy in html, you just use relative referencing instead of absolute referencing. I have been reading in the help file and they describe something called a "Hyperlink Base" that you can override in Excel advanced settings, but there is no example of how to make the links work without getting a path and file name saved with them. Since I couldn't do that, I was using VBA to retrieve the path and file name into the worksheet, and append them into the hyperlink address, but still not working. I'm going to search on Mr Excel to see if I can find some examples using Hyperlink base.

 

[6StringJazzer]

I cannot reproduce your success. I am trying to do what you describe, with no blanks anywhere, in the same folder, and I just get "Cannot open specified file." So I'm sorry but I am at a dead end.

 

[FracinDean]

@6StringJazzer thank you for sweating through this one with me. I am sorry for all your trouble, but if you are on this forum, you must like solving code puzzles.

I could not get what I wanted using just the HYPERLINK function either. I found something that looked promising using the ADD method of the HYPERLINKS object in a thread on this forum called "Hyperlinks.Add not pointing to expected address". I don't know how to make a link to that post which is a tad ironic considering what this thread is about.

Anyway, I was able to get what I want by putting the following code in the Activate event procedure for the worksheet where I was trying to insert the hyperlinks:

    Dim lRange As Range, c As Variant
    Set lRange = Range("sheetLinksRange")
    For Each c In lRange
        c.Select
        mySheet = c.Value
        Selection.ClearHyperlinks
        ActiveSheet.Hyperlinks.Add _
            Anchor:=Selection, _
            Address:="", _
            SubAddress:="'" & mySheet & "'!$A$1", _
            TextToDisplay:=mySheet
    Next c

This code is giving me success no matter whether the path or file name or worksheet names have blanks in them. I don't care that I had to do it in VBA instead of with the HYPERLINK function.

 

[6StringJazzer]

For what you are doing I do think that's a better solution. I was focusing on what was wrong with the HYPERLINK function rather than starting fresh with VBA. I have written code similar to what you show here.