Why does my macro to store open workbook paths work in Excel 2010 but not Excel 2016?

[anasttin1]

Hi,

The macro below is intended to make a clickable hyperlink to all open files in the source spreadsheet. It works fine in Excel 2010 but not at all in Excel 2016 - I think because 2016 uses separate instances of Excel. Can someone assist with a re-write to make it work in Excel 2016? Thanks so much in advance.

 Dim wb As Workbook
    Dim i As Long
   
    With Sheet1.Range("A:A")
        .ClearContents
        i = 0
        For Each wb In Application.Workbooks
            If wb.Windows(1).Visible Then
                i = i + 1
                Cells(i, 1).FormulaR1C1 = "=hyperlink(" & Chr(34) & wb.Path & "\" & wb.Name & Chr(34) & ")"
            End If
        Next wb
    End With

 

[Fluff]

That code works fine for me in 365, although you are missing a period in front of Cells

.Cells(i, 1).FormulaR1C1 = "=hyperlink(" & Chr(34) & wb.Path & "\" & wb.Name & Chr(34) & ")"

I can see no reason why it would not work in 2016. Do you get any error messages?

 

[anasttin1]

Sorry about the late reply.

This seems to work now. Not sure why it didn't the first try.

Thank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Dan_W]

Sorry about the late reply.

This seems to work now. Not sure why it didn't the first try.

Thank you

Glad it's working. In terms of why it didn't seem to work the first time, I wonder if that might be because it puts the information on Sheet1, and you were perhaps looking at another sheet? That happens to me all the time.

 

[anasttin1]

Not sure but thanks all.