creating a hyperlink in a WS cell

[rickblunt]

Greetings, below is a snippet from some code that I am using to create a backup copy of a WS, I haven't included everything, just the first part that I believe will pertain to my question. I have been noodling and searching for this for a bit but I am not having much luck.

You will notice that when I do my SaveAs I create a new file address (the string in red) for the backup copy of the WS. I log this event on my "Inspection Log" WS on my main WB. I am wondering if when I create that backup copy of the WS, if I could also make that same file address a hyperlink in column "E" on my inspection log sheet. that way if we want to pull up the WS to look at it we could do it from right there, not have to find the file on the backup server. I appreciate any input, thanks

Dim wb As Workbook
  
  Application.EnableEvents = False
  
  Sheets("Fire Extinguisher Inspection").Copy
  Set wb = ActiveWorkbook
  With wb
    Application.DisplayAlerts = False
    .SaveAs _
         [COLOR=#ff0000]"N:\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Fire Extinguishers\Fire Extinguisher  " _
          & Format(Now, "yy_mmdd") & ".xlsx", FileFormat:=51[/COLOR]
   .Close True
   Application.DisplayAlerts = True
  End With
    
' This subset creates the log entry for the completed inspection and then sends out the email.
    
    Worksheets("Inspection Log").Activate
    
    myValue = InputBox("Would you like to add a comment to the report?", "Report Comments")
   Range("Coding!S3").Value = myValue


    With Sheets("Inspection Log")
 
     nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
  
     .Range("A" & nextrow) = "Fire Extinguisher Inspection"
     .Range("B" & nextrow) = Worksheets("Coding").Range("M5").Value
     .Range("C" & nextrow) = Now()
     .Range("D" & nextrow) = Worksheets("Coding").Range("M4").Value
[COLOR=#ff0000]     .Range("E" & nextrow) = (this is where I would want the hyperlink reference to go)[/COLOR]
    End With

[I]'more code after this for other stuff.....[/I]

 

[Zubair Muhammad]

Hi,

Try this

[COLOR=#ff0000]
.Range("E" & nextrow).formula = "=Hyperlink(" & Chr(34) & yourfilepath & Chr(34) & ")"[/COLOR]

 

[rickblunt]

Thanks Zubair - that worked great