Save hyperlinked file to a cell

juangfd · Aug 31, 2011

I try to briefly explain my situation. I have a table where on a certain colum there are hyperlink to various type of file (mostly pdf). I am seeking for a way to save file of certain selected cells, faster than opening each file by clicking on the relative cell with hyperlink, and saving each file to the designated folder, which could be on the desktop on in any directory.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

>Thanks,</o

>
<o

></o

>
<o

>juangfd</o

>

Case_Germany · Sep 2, 2011

Hi,

that you can easily find out. Run the macro by pressing F8 and press F8 until the yellow marker is on the line "If rngRange.Hyperlinks.Count > 0 Then". Now you copy the following into the Immediate window - including the question mark:

Code:

?rngRange.Formula

and press return.

Then

Code:

?Split(rngRange.Formula, """")(1)

and press return.

What is displayed?

juangfd · Sep 6, 2011

I didn't quite understood the instruction.
I have two problems:
A) With hyperlink manually inserted. When I insert manually the hyperlink and run the macro with the code you provided me with, everything work well, but after saving the file the same macro doesn’t work as before. I noticed that when I save the file, by right-clicking on the cell where I just insert the hyperlink and select modify hyperlink, the address of the hyperlink is not exactly as I inserted manually. As example, when I insert manually the hyperlink, I write “server33\Data\Ancona\Office_A\example.pdf”. after saving the file in the hyperlink box the name becomes ”..\Office_A\example.pdf”. Is there a way to tell excel not to truncate/modify the name of the hyperlink, by changing some excel option? I use Windows 7 with Excel 2010 B) With Hyperlink with formula. The code doesn’t work, it creates the folder on the desktop without any file and doesn’t give me any message with error.

Thanks again

Case_Germany · Sep 7, 2011

Hi,

juangfd said:
Is there a way to tell excel not to truncate/modify the name of the hyperlink, by changing some excel option?

Yes:

Excel Options - Advanced - General click Button "Web Options..." click on the Files tab and remove the check in "Update links on save".

For the second problem, upload a sample file.

juangfd · Sep 8, 2011

[FONT=&quot]Excuse my ignorance, but how can I upload a sample problem in this forum? Or can I get your email?

thanks
[/FONT]

Case_Germany · Sep 9, 2011

Hi,

I sent you a PM.

Case_Germany · Sep 14, 2011

Hi,

here the solution:

Code:

Option Explicit
Private Declare Function MakeSureDirectoryPathExists _
    Lib "imagehlp.dll" (ByVal Pfad As String) As Long
Sub Main()
    On Error GoTo Fin
    Dim rngRange As Range
    With Foglio5 ' adapt
        MakeSureDirectoryPathExists _
            Environ("Userprofile") & "\Desktop\" & "Pippa\"
        For Each rngRange In Selection
            If rngRange.Hyperlinks.Count > 0 Then
                FileCopy rngRange.Hyperlinks.Item(1).Address, _
                    Environ("userProfile") & "\Desktop\" & _
                    "Pippa\" & Dir(rngRange.Hyperlinks.Item(1).Address)
                ElseIf rngRange.Formula Like UCase("=HYPER*") Then
                    FileCopy .Range("G1").Value & _
                        .Range("F2").Value & _
                        .Range("F1").Value & _
                        .Cells(rngRange.Row, 1).Value & _
                        .Cells(rngRange.Row, 2).Value & _
                        .Range("F3").Value, Environ("UserProfile") & _
                        "\Desktop\" & "Pippa\" & _
                        .Cells(rngRange.Row, 1).Value & _
                        .Cells(rngRange.Row, 2).Value & .Range("F3").Value
                End If
            Next rngRange
        End With
Fin:
    If Err.Number <> 0 Then MsgBox "Errore: " & _
        Err.Number & " " & Err.Description
End Sub

The link is formed by formula references.

Save hyperlinked file to a cell

juangfd

New Member

Case_Germany

Active Member

Excel Facts

juangfd

New Member

Case_Germany

Active Member

juangfd

New Member

Case_Germany

Active Member

Case_Germany

Active Member

Similar threads

Forum statistics

Share this page

Save hyperlinked file to a cell

juangfd

New Member

Case_Germany

Active Member

Excel Facts

juangfd

New Member

Case_Germany

Active Member

juangfd

New Member

Case_Germany

Active Member

Case_Germany

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock