HYPERLINKING PDF FILES HAVING NAMES IN COLUM TO A PDF FILE IN A FO

  • Thread starter Thread starter COOLGUY_IVRCL
  • Start date Start date
C

COOLGUY_IVRCL

I have around 150000 PDF files to hyperlink in excel from a folder. I have
put the file name in Column A. The file names of PDF file are same as in
Column A. Is there any function or macro which can hyperlink the files.

Can any one help me out of this problem.
 
Instead of the overhead of hyperlinks try this
Right click sheet tab>view code>insert this>modify your path to suit
Then, assuming you have
myfile typed in a cell and you double click on it the file in yourfolder
will open

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
ActiveWorkbook.FollowHyperlink Address:= _
"\yourfoldernamehere\" & Target.Value & ".pdf"
End Sub
 
If you've got 150,000 files listed in one sheet, I assume you're using Excel
2007 or 2010?
This code will take the entries in column A starting with whatever cell you
define in the code as 'firstName' in the code on to the end of the list (or
1st empty cell) and turn it into a hyperlink.

The code assumes that either the full path to the .pdf file(s) is part of
the entry in column A, or that they're all in a single folder somewhere.

If the full path is in the cell as part of the .pdf name, then use the line
of code that reads Const basePath = "", deleting the one that now reads
Const basePath = "C:\SomeFolder\AnotherFolder\PDFFiles\"

But if you need to add the path, then use that last one, changing the path
as required, and delete the 'Const basePath = "" line from the code.

Make a copy of your workbook, and test the code in the copy!

To put the code into your workbook: Open the workbook, press [Alt]+[F11]
and then choose Insert --> Module and copy the code and paste it into the
module presented to you. Then edit the code as needed to change/set the
values of firstName and basePath to meet your needs. Then close the VB
Editor. Choose the sheet with the files listed and use
Tools-->Macro-->Macros and choose the MakeHyperlinks macro and click the
[Run] button.


Sub MakeHyperlinks()
Dim listOfFiles As Range
Dim anyFile As Range
'set this next value to the
'address with the first .pdf
'filename in it
Const firstName = "A2"
'if you don't have the full path
'to the .pdf files in the cells
'in column A, then use this line
'of code
'changing the path as required
Const basePath = "C:\SomeFolder\AnotherFolder\PDFFiles\"
'but if the paths are already in the cells, then
'use this line of code instead
' remove the ' at the start of it to make it
' an active command, and remove the
' previous definition of basePath
'Const basePath = ""


Set listOfFiles = ActiveSheet. _
Range(firstName & ":" & _
ActiveSheet.Range(firstName).End(xlDown).Address)
For Each anyFile In listOfFiles
ActiveSheet.Hyperlinks.Add Anchor:=anyFile, _
Address:=basePath & anyFile.Value, _
TextToDisplay:=anyFile.Value
Next
Set listOfFiles = Nothing
End Sub
 
Very nice, I like that and hadn't even thought about such a method.

If the OP then formatted column A as underlined blue text, they'd even look
like hyperlinks.
 
I have used this for a long time from a MENU sheet to goto a filename typed
in a cell or to a sheet in the menu file. I have it so that it will
activate if already open.
 
Back
Top