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

 

[RedBeard]

If you step through the macro, does the ws1.Hyperlinks.Add(ws1, wb1.FullName, , , Left(wb1.Name, 8)) line give the expected result?

 

[khabi21]

It actually gives me the error as soon as through the Set wb2 = workbooks.open("****") line. But then when I switch back to the Data(25) = Left(wb1.name, 8) line, it runs through it no problem. So, there is not problem with that Set line. It's confusing.

 

[khabi21]

Actually, now when I try to run my macro with either line in for Data(25), once I get to the Set wb2 = workbooks.open line, it just runs the rest of the macro. It doesn't even let me step through it.

It still runs ok when I use the working Data(25) line, but it won't let me step line-to-line. Something fishy is going on.

 

[khabi21]

If you step through the macro, does the ws1.Hyperlinks.Add(ws1, wb1.FullName, , , Left(wb1.Name, 8)) line give the expected result?

Ok, I had to restart my computer for some reason and now I can step through it. Once I get to the ws1.Hyperlinks.Add line, it faults out with the "Object invoked has disconnected from clients" error that I stated above.

 

[RedBeard]

If you pipe that line to a separate variable instead, does that work?

 

[khabi21]

If you pipe that line to a separate variable instead, does that work?

I'm not sure what you mean by that??
Sorry I'm not a programmer, I just learn as I go.

I think it has to do with the fact that I'm trying to store a hyperlink into an array. Something tells me that's not possible, although I have no reasoning why. I wanted to do it that way because it would be the simplest and neatest solution, but I'm not having any luck.

Instead I'm going to try to change the For loop near the bottom of the code, to add a hyperlink to that file when i = 25. Here is what I'm thinking of replacing the current For loop with. I'm still having issues with the i=25 line, when I step through it gives me an "Object Required" error.

    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 24
                    EmptyRow.Offset(0, i - 1).Value = Application.Index(Data, i)
            Next i
                For i = 25 To 25
                    EmptyRow.Offset(0, i - 1).Value = .Hyperlinks.Add(Application.Index(Data, i), 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
     Else
        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(Application.Index(Data, i), 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
    End If

 

[RedBeard]

On the line with EmptyRow.Offset(0, i - 1).Value, remove .Value and see if that doesn't do it.

 

[khabi21]

On the line with EmptyRow.Offset(0, i - 1).Value, remove .Value and see if that doesn't do it.

Yeah, I tried that, and it didn't work. I also tried changing it to this:

  For i = 25 To 25
            EmptyRow.Offset(0, i - 1).Value = ws2.Hyperlinks.Add(EmptyRow.Offset(0, i - 1), wb1.FullName, , , Data(i))

But I still can't get it to work. When I switch it to this new line, it gives me an "Application-Defined or Object-Defined Error".

 

[khabi21]

I tried replacing it with this just to see if it is the Anchor that is messing it up:

For i = 25 To 25
       EmptyRow.Offset(0, i - 1).Value = ws2.Hyperlinks.Add(Range("A2"), wb1.FullName, , , Data(i))

It still gave me the "Application-Defined or Object-Defined Error", but it did actually place a hyperlink to the correct file in cell A2. However, it did not replace the text that was in "A2" with the text from Data(i).