Hyperlink more than one cell at a time?

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.

No, only one hyperlink per cell.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote in
message news:E0E65543-ED8F-406D-90A8-D0F59E644252@microsoft.com...
> 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.

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" wrote:

> No, only one hyperlink per cell.
>
> --
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>
>
> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote in
> message news:E0E65543-ED8F-406D-90A8-D0F59E644252@microsoft.com...
> > 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.
>
>
>

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?

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


"beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote in
message news:91311195-8025-4C97-B2A5-6598FD86573F@microsoft.com...
>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" wrote:
>
>> No, only one hyperlink per cell.
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>
>>
>> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote
>> in
>> message news:E0E65543-ED8F-406D-90A8-D0F59E644252@microsoft.com...
>> > 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.
>>
>>
>>

Thanks for the help, answers below

"Zack Barresse" wrote:

> 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.

> Are there any specifics/requirements on the friendly names of your
> hyperlinks? Not sure what a friendly name is.

> Any other requirements or details? I have a column with the file names listed all the down. I want to have a hyperlink in each cell under that column so that when I click on the filename, that file opens. It's easy to do one at a time, but I'd like to be able to automate the linking. Ideally, I'd have a CD with one folder containing all the files and one excel sheet containing all the file info. When I click on the link in Excel, the original file would open for viewing.
>
> --
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>
>
> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote in
> message news:91311195-8025-4C97-B2A5-6598FD86573F@microsoft.com...
> >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" wrote:
> >
> >> No, only one hyperlink per cell.
> >>
> >> --
> >> Regards,
> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >>
> >>
> >> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote
> >> in
> >> message news:E0E65543-ED8F-406D-90A8-D0F59E644252@microsoft.com...
> >> > 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.
> >>
> >>
> >>
>
>
>

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.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)



"beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote in
message news:3F76D71F-BD01-4EE6-891A-B85CB692FE20@microsoft.com...
> Thanks for the help, answers below
>
> "Zack Barresse" wrote:
>
>> 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.
>
>> Are there any specifics/requirements on the friendly names of your
>> hyperlinks? Not sure what a friendly name is.
>
>> Any other requirements or details? I have a column with the file names
>> listed all the down. I want to have a hyperlink in each cell under that
>> column so that when I click on the filename, that file opens. It's easy
>> to do one at a time, but I'd like to be able to automate the linking.
>> Ideally, I'd have a CD with one folder containing all the files and one
>> excel sheet containing all the file info. When I click on the link in
>> Excel, the original file would open for viewing.
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>
>>
>> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote
>> in
>> message news:91311195-8025-4C97-B2A5-6598FD86573F@microsoft.com...
>> >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" wrote:
>> >
>> >> No, only one hyperlink per cell.
>> >>
>> >> --
>> >> Regards,
>> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>> >>
>> >>
>> >> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com>
>> >> wrote
>> >> in
>> >> message news:E0E65543-ED8F-406D-90A8-D0F59E644252@microsoft.com...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>

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.

beginnerExceluser wrote:
>
> 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.

--

Dave Peterson

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" wrote:

> 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.
>
> --
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>
>
>
> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote in
> message news:3F76D71F-BD01-4EE6-891A-B85CB692FE20@microsoft.com...
> > Thanks for the help, answers below
> >
> > "Zack Barresse" wrote:
> >
> >> 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.
> >
> >> Are there any specifics/requirements on the friendly names of your
> >> hyperlinks? Not sure what a friendly name is.
> >
> >> Any other requirements or details? I have a column with the file names
> >> listed all the down. I want to have a hyperlink in each cell under that
> >> column so that when I click on the filename, that file opens. It's easy
> >> to do one at a time, but I'd like to be able to automate the linking.
> >> Ideally, I'd have a CD with one folder containing all the files and one
> >> excel sheet containing all the file info. When I click on the link in
> >> Excel, the original file would open for viewing.
> >>
> >> --
> >> Regards,
> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >>
> >>
> >> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote
> >> in
> >> message news:91311195-8025-4C97-B2A5-6598FD86573F@microsoft.com...
> >> >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" wrote:
> >> >
> >> >> No, only one hyperlink per cell.
> >> >>
> >> >> --
> >> >> Regards,
> >> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >> >>
> >> >>
> >> >> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com>
> >> >> wrote
> >> >> in
> >> >> message news:E0E65543-ED8F-406D-90A8-D0F59E644252@microsoft.com...
> >> >> > 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)



"beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote in
message news:24A4F30A-95F5-4D2B-8118-F349B68BC4D6@microsoft.com...
> 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" wrote:
>
>> 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.
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>
>>
>>
>> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com> wrote
>> in
>> message news:3F76D71F-BD01-4EE6-891A-B85CB692FE20@microsoft.com...
>> > Thanks for the help, answers below
>> >
>> > "Zack Barresse" wrote:
>> >
>> >> 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.
>> >
>> >> Are there any specifics/requirements on the friendly names of your
>> >> hyperlinks? Not sure what a friendly name is.
>> >
>> >> Any other requirements or details? I have a column with the file
>> >> names
>> >> listed all the down. I want to have a hyperlink in each cell under
>> >> that
>> >> column so that when I click on the filename, that file opens. It's
>> >> easy
>> >> to do one at a time, but I'd like to be able to automate the linking.
>> >> Ideally, I'd have a CD with one folder containing all the files and
>> >> one
>> >> excel sheet containing all the file info. When I click on the link in
>> >> Excel, the original file would open for viewing.
>> >>
>> >> --
>> >> Regards,
>> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>> >>
>> >>
>> >> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com>
>> >> wrote
>> >> in
>> >> message news:91311195-8025-4C97-B2A5-6598FD86573F@microsoft.com...
>> >> >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" wrote:
>> >> >
>> >> >> No, only one hyperlink per cell.
>> >> >>
>> >> >> --
>> >> >> Regards,
>> >> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>> >> >>
>> >> >>
>> >> >> "beginnerExceluser" <beginnerExceluser@discussions.microsoft.com>
>> >> >> wrote
>> >> >> in
>> >> >> message news:E0E65543-ED8F-406D-90A8-D0F59E644252@microsoft.com...
>> >> >> > 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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

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

"Dave Peterson" wrote:

> 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.
>
> beginnerExceluser wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>