Pull Data Validated List from Closed Workbook-CodePudding

I am trying to pull a data-validated list using a named range in a closed workbook. Both the source and active file sit on SharePoint. I don't have any connection problems between the workbooks, they otherwise link fine. But, for some reason, this code is not working whether the source workbook is closed or open. Can someone help me understand what I'm doing wrong?

Option Explicit

Sub UpdateDataValidation()

    Dim ID_Range As String
      
    Dim sourcePath As String
    sourcePath = "sharepoint site url" 'change the path accordingly

    Dim sourceFileName As String
    sourceFileName = "workbook name" 'change the file name accordingly

    Dim sourceSheetName As String
    sourceSheetName = "Sheet1" 'change the sheet name accordingly

    Dim sourceReference As String
    sourceReference = "ID_Range" 'change the reference accordingly"

    Dim currentCell As Range
    Dim currentValue As String
    Dim validationList As String

    validationList = ""
    For Each currentCell In Worksheets(1).Range(sourceReference) 'any worksheet reference will do for our purposes here
        currentValue = ExecuteExcel4Macro("'" & sourcePath & "[" & sourceFileName & "]" & sourceSheetName & "'!" & currentCell.Address(, , xlR1C1))
        validationList = validationList & "," & currentValue
    Next currentCell

    validationList = Mid(validationList, 2)

    With ThisWorkbook.Sheets("Sheet2").Range("K5:K504").Validation 'change the sheet name and range accordingly
        .Delete
        .Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Formula1:=validationList
    End With

End Sub

My goal is to used the named range ID_Range vs. a hard-coded range so that the data validation list can grow/contract as needed. I am running an on-save code to trigger near-constant updates of this macro, and no issues there.

I would appreciate any help in getting the code to work!

CodePudding user response：

It looks like your code is missing a couple of quotation marks. Try the code below:

Option Explicit

Sub UpdateDataValidation()

Dim ID_Range As String
  
Dim sourcePath As String
sourcePath = "sharepoint site url" 'change the path accordingly

Dim sourceFileName As String
sourceFileName = "workbook name" 'change the file name accordingly

Dim sourceSheetName As String
sourceSheetName = "Sheet1" 'change the sheet name accordingly

Dim sourceReference As String
sourceReference = "ID_Range" 'change the reference accordingly"

Dim currentCell As Range
Dim currentValue As String
Dim validationList As String

validationList = ""
For Each currentCell In Worksheets(1).Range(sourceReference) 'any worksheet reference will do for our purposes here
    currentValue = ExecuteExcel4Macro("'" & sourcePath & "[" & sourceFileName & "]" & sourceSheetName & "'!" & currentCell.Address(, , xlR1C1))
    validationList = validationList & "," &

CodePudding user response：

Thanks for the quick response, but that did not work. Please note that the line of code that is breaking is the one that starts with "For Each currentCell..."