Creating Macro or Code Help?

[Jeni Q]

Greetings,

I have a user who is doing a repetitive action in a spreadsheet
and I'm wondering if there's a better way to do what she's doing. Let me
explain the process.

Sue has a folder that contains 250-300 *.PDF files that have a naming
convention like such: GR005843.pdf. In this folder she also has .xls file
that contains fields like Filename, Date, Last Name, etc.
Each file listed in the Filename column corresponds to a PDF file in the
folder and displays the exact title of the file, e.g. "GR005843.pdf".
Currently, she goes to the first data cell in the column, A2, and inserts a
hyperlink into the XLS file by right-clicking and choosing Hyperlink. In the
Insert Hyperlink dialog box, Link to: Existing File or Web Page is
automatically selected. Look in: defaults to Current Folder (which is good
because that's where the files are). The Text to Display: defaults to the
text that is already in the cell. She types in the file name (GR005843.pdf)
in the Address field and chooses OK. Now the text that was in that cell is
replaced by a hyperlink to the corresponding file in the same folder. Then
she moves to A3 and repeats the same steps. She does this about 250-300
times, depending on how many files are in the monthly batch.

I feel like there should be a better way to do this but can't figure out how
a recorded macro could do so. It's the getting to the next cell part that
confuses me. I'm guessing it'll take some VBA code, but I'm not well versed
in that. Can anyone provide some suggestions or advice for me? Can you tell
me if what I'm trying to do is impossible?

I'll be happy to answer any questions or clarify something I did not explain
well.
Thanks in advance for your help.

Jeni Q

 

[Ken Wright]

One way is to use the hyperlink function =HYPERLINK()

Assume I have an mp3 file for example in the folder D:\4mydata called
wow.mp3 and in an excel file I have that text of wow.mp3 in say A2. Then if
in cell B2 i put the following formula:-

=HYPERLINK("D:\4mydata\"&A2,A2)

then I can now click on B2 and it will link straight to the file. I can
also use a formula to get me the filepath and then use that in the formula
as opposed to hardwiring in the path.


In your scenario, assuming the Excel file is in the SAME folder, then
assuming all your filenames are in Col A, starting A2, put the following
formula in cell B1:-

=MID(CELL("filename",$A$1),1,FIND("[",CELL("filename",$A$1))-1)

Now assuming your names start A2, and that you want the links in say Col D,
in D2 put the following formula and copy down as far as needed:-

=HYPERLINK($B$1&A2,A2)

This should give you a list of hyperlinks in about 30 seconds vs doing them
one by one in 6 hours or so.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Jeni Q]

Ken,
Wow, thanks!!

Unfortunately, I'm having a small problem. I think I've followed your
instructions to the word, but I'm getting a VALUE! error in B1 where I've
entered the filepath formula.

Here is the current formula:
=MID(CELL("filename",$A$1),1,FIND("[",CELL("filename",$A$1))-1)
I've stepped through a formula evaluation and here's what I learned.
+ First it translates CELL("filename",$A$1) into
T:\Geddes\Resumes\reslist.csv.
+ Then it changes the second CELL("filename",$A$1) into
T:\Geddes\Resumes\reslist.csv.
+ Next it tries to translate FIND("[",T:\Geddes\Resumes\reslist.csv) but
winds up with #VALUE!

I hope that makes sense. I'm not sure what's going on. I don't quite
understand the purpose of the open bracket in the FIND statement.

So then I deleted the filepath formula and the hyperlink formula
=HYPERLINK(A2,A2)
worked! I just copied the formula down the column and it worked just fine.
Is that because the default file location for a hyperlink is in the current
folder?
The only problem with this method is that I have to leave column A intact. I
can't delete it, which I'd like to do because it's redundant to have two
columns that have the exact same text in them.

So, any advice?
Thanks a lot,
Jennifer


: One way is to use the hyperlink function =HYPERLINK()
:
: Assume I have an mp3 file for example in the folder D:\4mydata called
: wow.mp3 and in an excel file I have that text of wow.mp3 in say A2. Then
if
: in cell B2 i put the following formula:-
:
: =HYPERLINK("D:\4mydata\"&A2,A2)
:
: then I can now click on B2 and it will link straight to the file. I can
: also use a formula to get me the filepath and then use that in the formula
: as opposed to hardwiring in the path.
:
:
: In your scenario, assuming the Excel file is in the SAME folder, then
: assuming all your filenames are in Col A, starting A2, put the following
: formula in cell B1:-
:
: =MID(CELL("filename",$A$1),1,FIND("[",CELL("filename",$A$1))-1)
:
: Now assuming your names start A2, and that you want the links in say Col
D,
: in D2 put the following formula and copy down as far as needed:-
:
: =HYPERLINK($B$1&A2,A2)
:
: This should give you a list of hyperlinks in about 30 seconds vs doing
them
: one by one in 6 hours or so.
:
: --
: Regards
: Ken....................... Microsoft MVP - Excel
: Sys Spec - Win XP Pro / XL 97/00/02/03
:
: ------------------------------­------------------------------­------------
----
: It's easier to beg forgiveness than ask permission
: ------------------------------­------------------------------­------------
----
:
:
:
:
: : > Greetings,
: >
: > I have a user who is doing a repetitive action in a spreadsheet
: > and I'm wondering if there's a better way to do what she's doing. Let me
: > explain the process.
: >
: > Sue has a folder that contains 250-300 *.PDF files that have a naming
: > convention like such: GR005843.pdf. In this folder she also has .xls
file
: > that contains fields like Filename, Date, Last Name, etc.
: > Each file listed in the Filename column corresponds to a PDF file in the
: > folder and displays the exact title of the file, e.g. "GR005843.pdf".
: > Currently, she goes to the first data cell in the column, A2, and
inserts
: > a
: > hyperlink into the XLS file by right-clicking and choosing Hyperlink. In
: > the
: > Insert Hyperlink dialog box, Link to: Existing File or Web Page is
: > automatically selected. Look in: defaults to Current Folder (which is
good
: > because that's where the files are). The Text to Display: defaults to
the
: > text that is already in the cell. She types in the file name
: > (GR005843.pdf)
: > in the Address field and chooses OK. Now the text that was in that cell
is
: > replaced by a hyperlink to the corresponding file in the same folder.
Then
: > she moves to A3 and repeats the same steps. She does this about 250-300
: > times, depending on how many files are in the monthly batch.
: >
: > I feel like there should be a better way to do this but can't figure out
: > how
: > a recorded macro could do so. It's the getting to the next cell part
that
: > confuses me. I'm guessing it'll take some VBA code, but I'm not well
: > versed
: > in that. Can anyone provide some suggestions or advice for me? Can you
: > tell
: > me if what I'm trying to do is impossible?
: >
: > I'll be happy to answer any questions or clarify something I did not
: > explain
: > well.
: > Thanks in advance for your help.
: >
: > Jeni Q
: >
: >
: >
:
: