Hyperlink more than one cell at a time?

[Guest]

Is there a way to hyperlink multiple cells to multiple files without having
to link one at a time?

The cells contain the filename, I'd like to make each one a hyperlink so
that when you click on it the native file opens.

Thank you.

 

[Zack Barresse]

No, only one hyperlink per cell.

 

[Guest]

I only need one hyperlink per cell, but I have a whole column of cells I want
to hyperlink to a folder that contains the files. I know I can link the
whole column to one file. I'm just looking for a marco or something that
will let me link cell 1 to file 1, cell 2 to file 2 and so on without having
to do one at a time. Any idea how to do that?

 

[Zack Barresse]

Yes, but you have to be a little more specific in your particulars. Let me
ask a few questions here ...

Are you wanting to perform this only once? Or on-going?
Will you be hyperlinking every file in a specific folder?
Are there any other files in this folder?
Is it the same file location that you are putting the hyperlinks into?
Where is that one located?
Are there any specifics/requirements on the friendly names of your
hyperlinks?
Any other requirements or details?

 

[Guest]

Thanks for the help, answers below

Yes, but you have to be a little more specific in your particulars. Let me
ask a few questions here ...

Are you wanting to perform this only once? Or on-going? I have a program, Z-Print, that creates a log(csv) after you batch print a folder. There is a column that lists the filenames. That's the one I want to link back to the files that were printed. So once the log is generated I would only need to hyperlink the cells one time.

Will you be hyperlinking every file in a specific folder? Yes

Are there any other files in this folder? Yes, but I can remove the other files if it's a problem.

Is it the same file location that you are putting the hyperlinks into? Yes, same main folder, sometimes with subfolders.

Where is that one located? The folder can be on the network or the C: drive, whichever is easiest.

 

[Zack Barresse]

Let me specify a couple of things here. I will call the document you want
to insert the hyperlinks into as the Original Workbook. If all files are to
be contained in the same folder, you will need a way to differentiate them.
If nothing else we can just use the name of the Original Workbook.

If the filenames are already in cells in a column, then all we'll need is a
location. This of course would be a breeze if the files were in the same
location as the Orignial Workbook. If this is the case, post the range
these names are located in. If this isn't the case, it will most likely
prove to be a little more difficult and in-depth.

 

[Dave Peterson]

I'd use a helper column of =hyperlink() formulas:

Depending on what was in the original column:

=hyperlink(a1)
or
=hyperlink("file:////" & a1)
or
=hyperlink("File:////" & "C:\my documents\pdffiles\ " & a1)
or
=hyperlink("File:////" & "C:\my documents\pdffiles\ " & a1 & ".pdf")

And drag down.

 

[Guest]

Each file in the folder has a unique name, which is entered into the column
in the Original Workbook. Is that enough to differentiate the files?

I can easily save the Original Workbook into the folder where the files
reside. At the moment, the Original Workbook is in the directory above the
folder containing the files.

The range of the cells? (not sure if I'm following you here, this is the
column with the file names)
=$AA$2:$AA$85

Path to the files:
\\A-Todd\Excel links\Personal Folders\Work\Bigmar Subfolders\Cisplatin

A sample of the value in the cell (AA2):
Personal Folders\Work\Bigmar Subfolders\Cisplatin\0001.htm
(Note, it is almost the entire path)

So if I browse to the Cisplatin folder, there will be a file 0001.htm. I'd
like to have all the cells in column AA linked to the appropriate file named
in the cell.

I realize that this is a pretty complicated and I really appreciate the time
and effort you've given. Thank you.

 

[Zack Barresse]

Yes, that is the range I was referring to. And as long as you have a
defined folder for where these files are located, it doesn't matter if the
Original Workbook is in the same file or not, just as long as it's defined.
Let's try something like this ...

1) Hit Alt + F11, to enter the Visual Basic Editor (VBE)
2) Hit Ctrl + R, to open the Project Explorer (PE; if not already open)
3) Select your file/project in the PE (filename is bolded) **
4) Select Insert (menu) | Module
5) Copy/paste the code below to the right (blank) pane (aka Code Pane)
6) Hit Alt + Q, to return to Excel
7) Save changes before anything else is done.

This is assuming that your filenames are listed in your sheet like this ...

Book1.xls
Book2.xls
Book3.xls
etc.

If not, we'll need to add that.

Code to copy/paste:


Option Explicit

Sub InsertWorkbookHyperlinks()

Dim oWb as workbook
Dim oWs as worksheet
Dim rngLoop as range, cell as range
Dim str_oWbPath as string, str_Path as string

Set oWb = ThisWorkbook '** may change if you decide on Personal.xls
Set oWs = oWb.Sheets("Sheet1") 'Change to sheet with filenames in it
Set rngLoop = oWs.Range("AA2:AA85") 'no need for $ signs, it's always
absolute
str_oWbPath = oWb.Path
str_Path = "C:\YourPathHere\" 'Change your path to files, include the
end backslash

For Each cell in rngLoop
cell.Hyperlinks.Delete 'just in case..
cell.Hyperlinks.Add cell, strPath & cell.Value,
TextToDisplay:="Friendly name here"
'if you don't want a Friendly Name, you can omit that part and it
will hyperlink the cell contents
Next cell

Set oWb = Nothing 'general cleanup
Set oWs = Nothing
Set rngLoop = Nothing
Set cell = Nothing

Msgbox "Complete!" 'tells you that it's done

End Sub


Just change those things that are commented above (comments show green in
the VBE).
Note: There are a few ways to call this. Depending on how you want it
called will depend on how we proceed. So how would you like to run this
routine? Do you want a button for it? A custom menu or toolbar for it?
** This line of instruction (3) can change, depending on the above
statement. If you want this accessible to all workbooks, you would need to
put this in your Personal.xls file, which will allow it to be accessible
globally throughout Excel. If it's only [Original] Workbook specific, then
the directions stand as-is.

HTH

 

[Guest]

Dave, thanks for the help, the simple answer worked the best for me. Macros
are a little out of my league so far.