Relative path

[navic]

When I use the absolute path everything is ok.
All workbooks are in the same folder.
But when I want to use the relative path of an active workbook then there is a problem.
It currently opens a window and requires you to select a specific workbook.
But I want VBA to do it in the background and pull data from a defined closed workbook.

This is a simplified example, in principle I need to pull data sometime from 2 workbooks and sometimes from 5 workbooks.
I use this VBA code to pull out of two workbooks. I call it twice with a different workbook defined.

How to set the relative path, regardless of which folder contained workbook.

    Dim mydata As String
    Dim sPath As String
    Dim sFileName As String
    Dim sSheetName As String
    Dim sRange As String
    
    'sPath = "C:\Temp\" 'absolute path of folder where is closed wbk '-> this work
    'sPath = ThisWorkbook.Path 'relative path of folder where is closed wbk '-> ???
    sPath = ActiveWorkbook.Path 'relative path of folder where is closed wbk '-> ???
    
    sFileName = "Book2.xlsm" 'name of wbk
    sSheetName = "Product" 'name of source Sheet which copying
    sRange = "B3:J115" 'range for copy
    
    'data location & range to copy
    mydata = "='" & sPath & "[" & sFileName & "]" & sSheetName & "'!" & sRange
    
    With ThisWorkbook.Worksheets("Products1").Range("B3:J115")
        .Formula = mydata
        .Value = .Value
    End With

Can anyone correct the VBA code?

 

[Marc L]

Just checking the final string you must see you just forgot to add a path separator between the Path property and the file name …​

​

 

[navic]

OMG, thank you @Marc_L
My brain stopped after several hours working