Hyperlinks and macros

QuestionEdit

I have a sheet that has a number/letter combo in column C that displays a file number I have to look up and create a link to i.e.(EO 974 708 555 US)(note number is downloaded with spaces, maybe I would have to get rid of them for this to work?) I also get a file that I currently save to a network drive(Z:\Signatures%202010\April\04.20.2010\EO974708555US.pdf) currently someone goes in and links these manually. I am wondering if their is a way to write a macro to hyperlink them automatically. The number and file name number change for each row I want to link. I also realize I might have to save them in a generic path without the dates, right now the file folder for date is created based on the date in the sheet. (Z:\Signatures%202010.

Here is what I get when I record the macro but I know that since each row is unique this is not going to work.

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 9/8/2010 by Jennifer ' ' Keyboard Shortcut: Ctrl+h '

   ActiveSheet.hyperlinks.Add Anchor:=Selection, Address:= _
       "..\Signatures%202010\August\8.26.2010\EO981198314US.pdf", TextToDisplay:= _
       "EO 981 198 314 US"

End Sub

AnswerEdit

Jennifer,

Sub macro1() Dim r As Range, cell As Range, s As String, dt As Date Set r = Range("C2", Range("C2").End(xlDown)) For Each cell In r

 dt = cell.Offset(0, -2).Value2
 s = "Z:\Signatures%202010\" & Format(dt, "mmmm\\m.d.yyyy\\") _
   & Replace(cell.Text, " ", "") & ".pdf"
 ActiveSheet.Hyperlinks.Add Anchor:=cell, _
     Address:=s

Next cell End Sub

I put some data such as you show in column C and some dates (8/20/2010) in column A and ran the macro. In the immediate window in the visual basic editor, I queried the address property of one of the resulting hyperlinks:

I copied the results: ? activesheet.hyperlinks(1).address Z:\Signatures 2010\August\8.20.2010\EO974708555US.pdf

I used a format command to produce the \August\8.20.2010\ from the corresponding date in that row. If that isn't correct, at least you see how it is done and you can make your own adjustments to get the correct path string.

So, tested and worked for me as I understand the requirement.