Export hyperlinks

[nuclearranger]

Hello,

I need some help writing a script/macro for excel to take an entire column out of excel and edit it along the way.

In general I have many columns but only ONE needs to be exported.
The value in each of thease cells looks like this-
=HYPERLINK("The Plots\"&X5&"_TT_"&Y5&".png",X5)
but you only see whatever "X5" is because thats the text variable.


As you can see they could reference many other cells in this.

When I copy the column it just gives me the text (Whatever is after the ",")

In short I am looking for a script that will take a column starting from a certain row and spit out the actual file-system links IE:
=HYPERLINK("The Plots\"&X5&"_TT_"&Y5&".png",X5)
would become
The Plots\secondary\_TT_Inner.png

It can export to notepad or another excel column as text it really doesn't matter.



I really apreciate any help I can get on this I have been staring at my screen for hours trying to figure this out. I really do not not know any VB so that compounds the problem. I hope I was descriptive enough in this.

~Thanks again

[DonkeyOte]

Given the links have been created via HYPERLINK it's a little more cumbersome than normal... the quickest fix would be to adjust your formula and remove the optional friendly_name from the function, eg:

=HYPERLINK("The Plots\"&X5&"_TT_"&Y5&".png")

You will find that Excel will at that point use the full link as the friendly_name (display text) by default - and thus you can pull your links by virtue of the cell's value (ie as you are now)

Without modifying the formulae - using your sample - you might get away with using Evaluate to pull the underlying link, eg:

Dim rngCell As Range, strLink As String
For Each rngCell In Range(...to be specified...)
    With rngCell
        strLink = .Parent.Evaluate(Split(.Formula, ",")(0) & ")")
    End With
Next rngCell

or something along those lines...

[nuclearranger]

This is what I have-

    Dim rngCell As Range, strLink As String
    For Each rngCell In Range("R5:R207")
    With rngCell
        strLink = .Parent.Evaluate(Split(.Formula, ",")(0) & ")")
    End With
Next rngCell

It crashes at ***strLink = .Parent.Evaluate(Split(.Formula, ",")(0) & ")")***

Im wondering if this outputs to anything? Is this set to replace the current cell with the full link? Thats fine I am just wondering.

Any help on getting this finished is greatly appreciated thanks again.

PS/EDIT-

This is a

RunTime Error "9"
SubScript out of range.

When it crashes and I move my mouse over StrLink and it has the value I want in it so I am not sure why its crashing. Again -Thanks

[DonkeyOte]

The earlier pseduo-code makes two critical assumptions, namely:

1 - a HYPERLINK function exists within each cell specified in standard form - ie not embedded as part of a bigger function

2 - That no comma shall appear within the Hyperlink address itself literally

The below:

Dim rngCell As Range, strLink As String
For Each rngCell In Range("R5:R207")
    With rngCell
        If Left(.Formula, 10) = "=HYPERLINK" Then
            strLink = .Parent.Evaluate(Split(.Formula, ",")(0) & ")")
            Debug.Print strLink
        End If
    End With
Next rngCell

will test for a basic HYPERLINK function but won't handle nuances should the function be contain embedded functions nor will it account for literal commas appearing within the hyperlink address.

If this remains an issue then I would suggest posting a representative sample file such that people can test.

Regards what the code does - the code provided simply generates (or attempts to generate) the requisite URL to a string variable - strLink
What you choose to do with the variable is of course up to you - for the sake of validation I added a Debug.Print line to the above so as to write results to the Immediate Window in VBE