Excel VBA - Getting Data source value

Question

Dear all,

I'm working on how to get the data source value below by VBA:

which is C:\Users\mynane\Desktop\SourceData\New\Country-Iso_Codes_Table.xlsx

Any idea?

Thanks

Answer 1

AFAIK there is no way to get that path for sure.

You can loop through the Workbook.Connections collection to get a WorkbookConnection and if you found a query you can access it using the Workbook.Queries collection to get each WorkbookQuery, but none of these objects has a property to get the related path.

The reason is that a path can be dynamic, e.g. stored inside a parameter table, so you never know which file is used at runtime.

Andreas.

Answer 2

Thanks for the feedback but my unknown value I need to detect is precisely this string value: C:\Users\mynane\Desktop\SourceData\New\Country-Iso_Codes_Table.xlsx

Answer 3

try...

Sub OpenWB()

Dim wb1 As Workbook, wb2 As Workbook

Set wb1 = ThisWorkbook

Dim sPath

sPath = "C:\Users\mynane\Desktop\SourceData\New\Country-Iso_Codes_Table.xlsx"

Set wb2 = Workbooks.Open(sPath)

End Sub