VBA - Add Hyperlink to Array - "Object invoked has disconnected from clients" Error

[khabi21]

Hello,
I have written this macro that takes data from one worksheet ("ws1") and puts it into an array. It then empties this array into another workbook ("wb2") in the next empty row. This part of the macro works perfectly, and has been working.

Now, I want to add another feature:
Referring to the code below, "Data(25)" in the array is a file name which references a specific file, and it is placed in a specific cell in "wb2" so the person can go back and find the file. Now, instead of just the file name being stored in "wb2", I actually want to store a hyperlink in "wb2".

For example, in the old macro, which works, it would place the file name "IML00001" in the correct column in "wb2". The person would then manually find this file in a separate folder and open it.
In the new macro, I'd like the "IML00001" which is placed in the specific cell in "wb2" to be a hyperlink.

I've tried a couple ways to do this, but I can't get it to work. In the code below, the Data(25) line is the old macro, which works when I run it. The 'Data(25) line that I commented out and bolded directly below it, does not work. It gives me the "Object invoke has disconnected from clients."

Any help would be appreciated.
****Ignore the stars asterisks in the wb2 line, I did that for privacy. That is not in the actual code and the actual code works when I'm not adding the new feature, so it is not a path issue.

Private Sub CopyDataToMatrix()    
'This macro copies the data from the process sheet & automatically pastes it into
'the matrix.


    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim Data(1 To 26)
    Dim EmptyRow As Range
    Dim strSearch As String
    Dim rngSearch As Range
    Dim rowNum As Integer
                
    Set wb1 = ActiveWorkbook
    Set wb2 = Workbooks.Open("****")
    
    Set ws1 = wb1.Sheets("ProcessData")
    Set ws2 = wb2.Sheets("2016")
    
    'Put all of the data into an array:
     Data(1) = ws1.Range("B57").Value     
     Data(2) = ws1.Range("B3").Value      
     Data(3) = ws1.Range("B4").Value      
     Data(4) = ws1.Range("B5").Value      
     Data(5) = ws1.Range("F7").Value      
     Data(6) = ws1.Range("B6").Value      
     Data(7) = ws1.Range("B7").Value     
     Data(8) = ws1.Range("F8").Value     
     Data(9) = ws1.Range("B8").Value      
     Data(10) = ws1.Range("B9").Value     
     Data(11) = ws1.Range("B10").Value    
     Data(12) = ws1.Range("F9").Value     
     Data(13) = ws1.Range("F4").Value     
     Data(14) = ws1.Range("F5").Value     
     Data(15) = ws1.Range("F6").Value     
     Data(16) = ws1.Range("G4").Value     
     Data(17) = ws1.Range("G5").Value     
     Data(18) = ws1.Range("G6").Value     
     Data(19) = ws1.Range("H4").Value     
     Data(20) = ws1.Range("H5").Value     
     Data(21) = ws1.Range("H6").Value     
     Data(22) = ws1.Range("I4").Value     
     Data(23) = ws1.Range("I5").Value     
     Data(24) = ws1.Range("I5").Value     
     Data(25) = Left(wb1.Name, 8)         
     '[B]Data(25) = ws1.Hyperlinks.Add(ws1, wb1.FullName, , , Left(wb1.Name, 8))    [/B]   
     Data(26) = ws1.Range("A45").Value   


     'Place the data into the IM Matrix file:
     
     'Look to see if the row already exists with the current file name then overwrite it:
     strSearch = Left(wb1.Name, 8)
     Set rngSearch = ws2.Range("Y:Y")
     
     If Application.CountIf(rngSearch, strSearch) > 0 Then
        rowNum = Application.Match(strSearch, rngSearch, 0)
        With ws2
            Set EmptyRow = .Cells(rowNum, 1)
            For i = LBound(Data) To UBound(Data)
                EmptyRow.Offset(0, i - 1).Value = Application.Index(Data, i)
            Next i
        End With
     Else
        With ws2
            Set EmptyRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
            For i = LBound(Data) To UBound(Data)
                EmptyRow.Offset(0, i - 1).Value = Application.Index(Data, i)
            Next i
        End With
    End If
            
     'Close & save IM Matrix file:
     wb2.Close SaveChanges:=True


End Sub

 

[khabi21]

So this setup actually does place the hyperlink where and how I want it. However, it still gives me the "Application-Defined or Object-Defined Error" when I step through the ws2.Hyperlinks.Add line, even though it does exactly what I ask it to do.

With ws2
       Set EmptyRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
             For i = LBound(Data) To 24
                EmptyRow.Offset(0, i - 1).Value = Application.Index(Data, i)
       Next i
             For i = 25 To 25
                EmptyRow.Offset(0, i - 1).Value = ws2.Hyperlinks.Add(EmptyRow.Offset(0, i - 1), wb1.FullName, , , Data(i))
       Next i
             For i = 26 To UBound(Data)
                EmptyRow.Offset(0, i - 1).Value = Application.Index(Data, i)
       Next i
End With

Any help please.

 

[khabi21]

Started a new thread since the title no longer applies and lack of interest.

http://www.mrexcel.com/forum/excel-...link-dynamic-cell-another-workbook-error.html