Import data and Attach Hyperlink

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
547
Hi - I'm currently running code that opens workbook2 copies data and then closes workbook2. Is there a way to insert a hyperlink for workbook2 into column "B" of the row the data is being pasted? Here's the code I'm using to import.

Code:
Dim ws1 As WorksheetSet ws1 = Sheet1


sPath = "\\Server\Folder1\" & ws1.Range("A5")


If SetFilePath(sPath) = 0 Then
    MsgBox "Error in setting the path - " & sPath
Else
    sFile = Application.GetOpenFilename("Excel,*.xls;*.xlsx")
End If


If sFile <> False Then
    Set WB = Workbooks.Open(sFile)
Else
    MsgBox "No File Selected"
        Exit Sub
End If


Set ws2 = WB.Sheets("Data")
Set CsNm = WB.Sheets("Data").Range("A1")


Set found = ws1.Range("B:B").Find(what:=CsNm, LookIn:=xlValues, lookat:=xlWhole)
If found Is Nothing Then
    fRow = InputBox("Name match not found - Enter Row")
    Else
    fRow = found.Row
End If




With ws2


    .Range("J8").Copy
    ws1.Range("C" & fRow).PasteSpecial xlPasteValues


    .Range("J9").Copy
    ws1.Range("D" & fRow).PasteSpecial xlPasteValues


    .Range("J3").Copy
    ws1.Range("E" & fRow).PasteSpecial xlPasteValues


End With


WB.Close False
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
547
I added this code right before WB.Close False and it works perfectly.

Code:
ws1.Hyperlinks.Add Anchor:=ws1.Range("B" & fRow), Address:=sFile
 

Watch MrExcel Video

Forum statistics

Threads
1,095,372
Messages
5,444,074
Members
405,265
Latest member
Iram

This Week's Hot Topics

Top