Changing Query Source with VBA Help: Excel file on network path

[collin8579]

Good morning,
I have queries that reference excel files on a network path,
In the attached picture my source is something like K:\ATT Cognos\STX Pace Data.xlsx
I need to be able to change it to different markets and refresh the query without having to go into the query manually,
I am looking for VBA solution, and have tried things like
With ActiveSheet.QueryTables.Add(Connection:=FilePath, Destination:=Range("$A$1"))

Worksheets(1).QueryTables(1) _ Connection := "TEXT;C:\My Documents\19980331.txt"

I haven't been able to figure out a solution that works and any help would be appreciated.

 

[sandy666]

if your sources have the same structure and headers then you can define a parameter which can be selected from DV list from the Excel sheet

 

[collin8579]

if your sources have the same structure and headers then you can define a parameter which can be selected from DV list from the Excel sheet

I have the DV list already< its how to associate that with the query source that I am asking

 

[sandy666]

Does the DV list contain source paths?

try to adapt from this example

 

[collin8579]

Figured out how to do what I was trying to do: Advanced editor shows as follows.

let
Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(Filepath), null, true),

Where the filepath variable in excel is a cell that has the dynamic filepath name pop up.
works well

Thanks!