List external links and allow user to input desired cell location - what is wrong with this code?-CodePudding

I'm trying to list all of the external sources in an Excel worksheet but I would like the user to be able to select which cell they would like the links to begin to be listed in.

I've tried the following code but I just can't get it to work..

Sub ListExternalLink()

    Dim wb As Workbook
    Set wb = Application.ThisWorkbook
    
    xRln = InputBox("Which row #?", "Listing external link..", "Numbers Only")
    xCln = InputBox("Which column #?", "Listing external link..", "Numbers Only")
    
    If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
      For Each link In wb.LinkSources(xlExcelLinks)
        Application.ActiveSheet.Cells(xRln, xCln).Value = link
        xRln = xRln   1
      Next link
    End If
    
End Sub

What am I doing wrong?

Can anyone help me figure out how I can get this to work?

Thank you, in advance, for the help!!

CodePudding user response：

List Link Sources

Select a Cell

Sub ListExternalLinksCell()
    On Error GoTo ClearError
    
    Const pTitle As String = "List External Links"
    
    Dim defValue As String
    If TypeOf Selection Is Range Then
        defValue = Selection.Address
    End If
    
    On Error Resume Next
    Dim FirstCell As Range
    Set FirstCell = Application.InputBox("Select the first cell", _
        pTitle, defValue, , , , , 8)
    On Error GoTo ClearError
    
    If FirstCell Is Nothing Then
        MsgBox "Canceled by user.", vbExclamation, pTitle
        Exit Sub
    End If
    
    Set FirstCell = FirstCell.Cells(1)
    Dim wb As Workbook: Set wb = FirstCell.Worksheet.Parent
    
    Dim lSources As Variant: lSources = wb.LinkSources(xlExcelLinks)
    If IsEmpty(lSources) Then
        MsgBox "No link sources found.", vbExclamation, pTitle
        Exit Sub
    End If
    
    Dim lSource As Variant
    For Each lSource In lSources
        FirstCell.Value = lSource
        Set FirstCell = FirstCell.Offset(1)
    Next lSource

ProcExit:
    Exit Sub
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ProcExit
End Sub

Input Row and Column

Sub ListExternalLinksRC()
    On Error GoTo ClearError
    
    Dim xRln As Variant, xCln As Variant
    xRln = Application.InputBox("Which row #?", "Listing external link..", "Numbers Only", , , , , 1)
    If xRln = False Then Exit Sub
    xCln = Application.InputBox("Which column #?", "Listing external link..", "Numbers Only", , , , , 1)
    If xCln = False Then Exit Sub
    
    Dim xRow As Long: xRow = CLng(xRln)
    Dim xCol As Long: xCol = CLng(xCln)
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim Link As Variant
    If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
        For Each Link In wb.LinkSources(xlExcelLinks)
            Debug.Print Link
            ws.Cells(xRow, xCol).Value = Link
            xRow = xRow   1
        Next Link
    End If

ProcExit:
    Exit Sub
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ProcExit
End Sub