Pivot with added data model - how to enable file path/name change for the end user

I was wondering if anyone knows how to solve this:

In my project, user can provide sales data and use macro to summarise the data and calculate market share. The macro itself, creates a pivot out of the data provided by user, then adds few DAX measures. Then copies the pivot into a new sheet as plain text (with a bit of formatting).

This is the code responsible for creating a data model for pivot (and measures):

Dim NewScenario As Worksheet
Dim RangeForPivot As Range

Set NewScenario = Sheets(ActiveSheet.Index)
Set RangeForPivot = NewScenario.Range("A3", Range("A3").End(xlToRight).End(xlDown).End(xlDown).Offset(-1))


Workbooks("Award Document template.xlsm").Connections.Add2 _
    "ScenarioConnection_" & NewScenario.Name & "!" & RangeForPivot.Address, "", _
    "WORKSHEET;N:\02 - Trust Activity (New)\Northumbria NHS Foundation Trust\Service Line 1 - CRM & EP\CRM\CRM PQ074 2023\03-Pricing Analysis\[Award Document template.xlsm]" & NewScenario.Name _
    , NewScenario.Name & "!" & RangeForPivot.Address, 7, True, False

ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("ScenarioConnection_" & NewScenario.Name & "!" & RangeForPivot.Address), Version:=8).CreatePivotTable TableDestination:= _
        NewScenario.Name & "!" & "R100C42", TableName:="ScenarioPIVOT", DefaultVersion:=8

The issue: End user will make a copy of this template file from our team folder into the customer's folder where they begin the analysis and other activities. Unfortunately, name and location of this file is hardcoded and the macro will not work from a different location, or if the file has a different name without adjusting the code.

Has anyone had similar situation to this and found a solution please? Any suggestions would be greatly appreciated.

Solution

Assuming the code is stored in the template file (Award Document template.xlsm), and pivot tables are created in the same file.
ThisWorkbook is a workbook object of the template file.
ThisWorkbook.Name and ThisWorkbook.Path return the file name and location (path).

Dim NewScenario As Worksheet
Dim RangeForPivot As Range
Dim sPath As String

Set NewScenario = ActiveSheet
Set RangeForPivot = NewScenario.Range("A3", Range("A3").End(xlToRight).End(xlDown).End(xlDown).Offset(-1))

With ThisWorkbook
    sPath = "WORKSHEET;" & .Path & "\[" & .Name & "]"
    .Connections.Add2 _
        "ScenarioConnection_" & NewScenario.Name & "!" & RangeForPivot.Address, "", _
        sPath & NewScenario.Name _
        , NewScenario.Name & "!" & RangeForPivot.Address, 7, True, False

    .PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
            .Connections("ScenarioConnection_" & NewScenario.Name & "!" & RangeForPivot.Address), Version:=8).CreatePivotTable TableDestination:= _
            NewScenario.Name & "!" & "R100C42", TableName:="ScenarioPIVOT", _
            DefaultVersion:=8
End With