Saving Column with Hyperlink Function as a Hyperlink

[J Harper]

I have created a column of active hyperlinks using the HYPERLINK FUNCTION. I need a column of active hyperlinks without using the HYPERLINK FUNCTION. I have tried PASTE SPECIAL and cannot same the active link.

How can I copy the column and save as an active hyperlink without using the HYPERLINK FUNCTION?

UNACTIVATED FILE PATH	ACTIVATED FILE PATH USING HYPERLINK FUNCATION; EXAMPLE: HYPERLINK (A2)	NEED ACTIVATED PATH WITHOUT USING HYPERLINK FUNCTION
C:\users\downloads\temp\test_folder_dch\test 1.pdf	C:\users\downloads\temp\test_folder_dch\test 1.pdf
C:\Users\downloads\temp\test_folder_dch\test 2.pdf	C:\Users\downloads\temp\test_folder_dch\test 2.pdf
C:\Users\downloads\temp\test_folder_dch\test 3.pdf	C:\Users\downloads\temp\test_folder_dch\test 3.pdf
C:\Users\downloads\temp\test_folder_dch\test 4.pdf	C:\Users\downloads\temp\test_folder_dch\test 4.pdf

 

[johnnyL]

How about using a function/formula?

Public Function GetURL(Cel As Range) As String
    On Error Resume Next
    GetURL = Cel.Hyperlinks(1).Address
End Function

Then in a blank cell put the formula:
=GetURL(B1)

B1, in this case, would be to extract link from hyperlinked cell B1

 

[J Harper]

Currently there are NO HYPERLINKED CELLS (COLUMN A). COLUMN A is only text. I can open TEST 1.PDF by using the HYPERLINK FUNCTION in COLUMN B. BUT I need for COLUMN C to the ACTIVATED FULL PATH LINK to the pdf without going to each cell and putting in the link.
C:\users\downloads\temp\test_folder_dch\test 1.pdf

COLUMN A​	COLUMN B​	COLUMN C​
C:\users\downloads\temp\test_folder_dch\test 1.pdf	=Hyperlink(A1)	C:\users\downloads\temp\test_folder_dch\test 1.pdf
C:\users\downloads\temp\test_folder_dch\test 2.pdf	=Hyperlink(A2)	C:\users\downloads\temp\test_folder_dch\test 2.pdf

 

[johnnyL]

@J Harper, Can I ask why you don't want to use the commonly accepted hyperlink approach? Do you think that would take a long time to do =Hyperlink(A1) ... =Hyperlink(A2) ... etc?

 

[johnnyL]

If you just want to convert the text in Column A to clickable links, then you just need to select the range in Column A that you want to convert and then run the following vba macro code:

Public Sub ConvertSelectedRangeToHyperlinks()
'
    Dim Cell As Range
'
    For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
        If Cell <> "" Then
            ActiveSheet.Hyperlinks.Add Cell, Cell.Value
        End If
    Next
End Sub

What exactly is your goal?

 

[J Harper]

The commonly used accepted hyperlink approach using =Hyperlink(A1) ... =Hyperlink(A2) ... etc doesnt seem to work. I need to be able read the column and perform a FILECOPY. The "normal approach" as noted in COLUMN B gives an error. SO, I think i need COLUMN C with the fully designated hyperlink. The MACRO that I am using is listed below. The MACRO works when I manually add the full path BUT DOES not work using COLUMN B with the "normal approach" as it was called above. I listed the MACRO that I am using below.

COLUMN A	COLUMN B	COLUMN C	COLUMN D (New file name that will be "filecopied" to C:\files\pdfs\
C:\users\downloads\temp\test_folder_dch\test 1.pdf	=Hyperlink(A1)	C:\users\downloads\temp\test_folder_dch\test 1.pdf	New File Name 1.PDF
C:\users\downloads\temp\test_folder_dch\test 2.pdf	=Hyperlink(A2)	C:\users\downloads\temp\test_folder_dch\test 2.pdf	New File Name 2.PDF

MACRO
Public Sub Copy_Hyperlinked_Files()
Dim destFolder As String
Dim lastRow As Long, r As Long
destFolder = "C:\files\pdfs\"
If Right(destFolder, 1) <> "\" Then destFolder = destFolder & "\"
With ActiveSheet
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For r = 2 To lastRow
FileCopy .Cells(r, "B").Hyperlinks(1).Address, destFolder & .Cells(r, "D").Value & ".pdf"
Next
End With

 

[johnnyL]

Sorry, I am still confused on what your goal is.

It sounds like you want to copy a file from a path that is designated in a cell, to a new location. I am confused on why the need for hyperlinks or active links just to copy a file?

 

[J Harper]

I assumed that I needed to link, if i simply have the name of the file will that work?

 

[johnnyL]

Yes

So change:

lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For r = 2 To lastRow
FileCopy .Cells(r, "B").Hyperlinks(1).Address,

to just:

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
FileCopy .Cells(r, "A"),

 

[J Harper]

So if I simply want to FILECOPY files in the folder "C:\users\o10339\downloads\temp\test_folder_dch\" (COLUMN C) as noted in spreadsheet below to a new folder that is designated in MACRO (i.e. C:\files\pdfs\), with the file name from COLUMN B?

By the way, I will not know the number of rows and need the process to continue until the last row.

COLUMN A​	COLUMN B​	COLUMN C​
#​	INVOICE #	PATH and FILE
1​	INV-ABC	C:\users\o10339\downloads\temp\test_folder_dch\test 1.pdf
2​	INV-DEF	C:\Users\o10339\downloads\temp\test_folder_dch\test 2.pdf
3​	INV-GHI	C:\Users\o10339\downloads\temp\test_folder_dch\test 3.pdf
4​	INV-KLM	C:\Users\o10339\downloads\temp\test_folder_dch\test 4.pdf

MACRO (What should change based on explanation above)
Public Sub Copy_Hyperlinked_Files()
Dim destFolder As String
Dim lastRow As Long, r As Long
destFolder = "C:\files\pdfs\"
If Right(destFolder, 1) <> "\" Then destFolder = destFolder & "\"
With ActiveSheet
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For r = 2 To lastRow
FileCopy .Cells(r, "B").Hyperlinks(1).Address, destFolder & .Cells(r, "D").Value & ".pdf"
Next
End With