I’d like to set up some VBA so that users are prompted to select 2 different filepaths, and those files are then set as the source in two different powerquery tables. I’d like to just set it so the powerquery code can refer to a variant defined by the VBA instead of referring to the named cell in the excel sheet. Any solutions?

My current workaround is that I have some VBA that prompts users to select select a file path and then another file path. Those filepaths are then set as two different cell values on a worksheet. I’ve named those cells (“PopManFilePath” and “ClinComFilePath”). The source in my powerquery is set as those named cells.
See VBA below:

Sub FileSelectandRefreshQueries()
    Dim answerpop As Integer
    answerpop = MsgBox("Please select your population management file in the following window. It must be a .csv file.", vbOKCancel, "Instructions")
    
    If answerpop = vbOK Then
        Dim PopManFileName As Variant
         PopManFileName = Application.GetOpenFilename(fileFilter:="CSV Files (*.csv),*.csv", Title:="Please select population management file")
     
            If PopManFileName <> False Then
            ThisWorkbook.Worksheets("FilePaths").Range("B1").Value = PopManFileName
            End If
    End If
    
    Dim answerclin As Integer
    answerclin = MsgBox("Please select your clinical complexity file in the following window. It must be a .csv file.", vbOKCancel, "Instructions")
    
        If answerclin = vbOK Then
        Dim ClinComName As Variant
         ClinComName = Application.GetOpenFilename(fileFilter:="CSV Files (*.csv),*.csv", Title:="Please select clinical complexity file")
        
            If ClinComName <> False Then
            ThisWorkbook.Worksheets("FilePaths").Range("B2").Value = ClinComName
            End If
        End If

ThisWorkbook.RefreshAll

End Sub

And this is my very simple powerquery code to set the source in one of my tables:

let
  LocalPath = Excel.CurrentWorkbook(){[Name="PopManFilePath"]}[Content]{0}[Column1],
  Source = Csv.Document(File.Contents(LocalPath),[Delimiter="#(tab)", Encoding=1200, QuoteStyle=QuoteStyle.None]),

I feel like there must be a better way to do this that doesn’t involve writing the file paths into a cell.