Create hyperlink using cell data

[jcmckeon]

Hello All,

How can I create a hyperlink to a network location from cell data. Want to populate customer name and part number into a link that will go to our network location and find all the folders starting with that part number. It may have a REV number or it may not.

We have hundreds of these so doing it manually is prohibitive. VB scripting is great but I'll take any suggestion. HELP!

HOW DO I POST FILE and IMAGE for example???

Thank you in advance

 

[Fishboy]

Hello All,

How can I create a hyperlink to a network location from cell data. Want to populate customer name and part number into a link that will go to our network location and find all the folders starting with that part number. It may have a REV number or it may not.

We have hundreds of these so doing it manually is prohibitive. VB scripting is great but I'll take any suggestion. HELP!

HOW DO I POST FILE and IMAGE for example???

Thank you in advance

Hi jcmckeon, welcome to the boards.

First and foremost, yes that can be achieved relatively easily with VBA (depending on how your data is laid out and what you want to incorporate into the hyperlink).

To share a document you should be able to upload to something like One Drive or Dropbox or Google Drive and share the link to the file in a forum post.

To include an image such as a screenshot just click on the "Insert Image" button at the top of the text window here on the forums like this:

It will ask you to provide a link to the image and will display whatever image is associated with that link as above. Again, this will mean uploading it somewhere first.

 

[jcmckeon]

Hello Fishboy, thank you for responding.

Here are the files - image to see what I talking about and a small sample Excel file.

https://www.dropbox.com/sh/wowxvx1m1akbghp/AAAoSeoxjjneJcZVf49flXzoa?dl=0

We have hundreds of files so doing this programmatically will be a BIG HELP.

Thank you in advance.

Jill

 

[Fishboy]

Hello Fishboy, thank you for responding.

Here are the files - image to see what I talking about and a small sample Excel file.

https://www.dropbox.com/sh/wowxvx1m1akbghp/AAAoSeoxjjneJcZVf49flXzoa?dl=0

We have hundreds of files so doing this programmatically will be a BIG HELP.

Thank you in advance.

Jill

Hi Jill,

Test the following code in a COPY of your workbook. This code is added to a standard module and applied to a button to run. This code assumes that all links will start with the same prefix of \\ds2\data\customer\customer docs\

Sub Build_Hyperlinks()
' Defines variables
Dim Cell, cRange, lRange As Range
' Defines LastRow as last row of column B containing data
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
' Sets check range as B2 to last row of B
    Set cRange = Range("B2:B" & LastRow)
' For each cell in check range
        For Each Cell In cRange
' If cell is not blank then...
            If Cell.Value <> "" Then
' Construct a new value in column M based on the preset path, plus the contents of columns B and E
                Range("M" & Cell.Row).Value = "\\ds2\data\customer\customer docs\" & Cell.Value & "\" & Range("E" & Cell.Row).Value
            End If
' Select column M of cell row
                Range("M" & Cell.Row).Select
' With selection
                    With Selection
' Create hyperlink based on cell value
                        ActiveSheet.Hyperlinks.Add Cell, Cell.Value
                    End With
' Move to next cell in check range
        Next Cell
End Sub

 

[jcmckeon]

Hi Jill,

Test the following code in a COPY of your workbook. This code is added to a standard module and applied to a button to run. This code assumes that all links will start with the same prefix of \\ds2\data\customer\customer docs\

Sub Build_Hyperlinks()
' Defines variables
Dim Cell, cRange, lRange As Range
' Defines LastRow as last row of column B containing data
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
' Sets check range as B2 to last row of B
    Set cRange = Range("B2:B" & LastRow)
' For each cell in check range
        For Each Cell In cRange
' If cell is not blank then...
            If Cell.Value <> "" Then
' Construct a new value in column M based on the preset path, plus the contents of columns B and E
                Range("M" & Cell.Row).Value = "\\ds2\data\customer\customer docs\" & Cell.Value & "\" & Range("E" & Cell.Row).Value
            End If
' Select column M of cell row
                Range("M" & Cell.Row).Select
' With selection
                    With Selection
' Create hyperlink based on cell value
                        ActiveSheet.Hyperlinks.Add Cell, Cell.Value
                    End With
' Move to next cell in check range
        Next Cell
End Sub

Hi Fishboy, this worked. Now taking it next step - can we take the Customer and Part Number and have it use Windows Explorer to search for the Customer name and part number so that I can get multiple Part number folders. We have several Revisions of the same part number

So referring back to the picture in GoogleDocs - is this possible or are we asking too much from Excel?