VBA to restore CELL(filename) value

[macfuller]

I use the following formula to return the location of the active "master" workbook.

=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)

This value is used in other formulas within the workbook as well as Power Query to identify the location of various source files in subfolders.

My problem is that I have a (lengthy) macro with multiple file operations on other "remote" workbooks, some in different folders. When the macro opens, alters, saves and subsequently closes the workbooks in those different folders the formula above changes to the location of the last-opened file. Subsequent operations depending on the master workbook locations fail.

I have tried Application.Volatile in my code after the remote workbook operations but the formula retains the last-saved location of the remote workbook. I don't want to run a calculation in the midst of quite a few Power Query operations either. I'm hoping there's an obvious way to reset the CELL(filename) value.

 

[selkov]

Might be helpful to post the code. I am not sure I understand where you are going here.

 

[shg]

Add a cell reference to the CELL function. Any cell in the workbook will do; most conveniently, the cell in which the formula appears.

 

[macfuller]

@shg, I'm not sure I understand adding another reference within the original cell. If $A$2 has the original formula showing the path of the workbook file are you saying I should have in (say) $A$3 a formula such as =CELL("address",$A$2)? I'm not sure how such a formula might be added to $A$2.
@selkov, the original formula returns the path of the master workbook, for example "c:\username\documents\Excel\dashboard". The formula is in a table along with other parameters for subfolders from the master location for data sources. Example

Name	Value
This file location	c:\username\documents\Excel\dashboard
ERP Source location	c:\username\documents\Excel\dashboard\Sources
Mail Source location	s:\email\exception log

<tbody>
</tbody>

When my macro uses the value for "Mail Source location" to open a file such as s:\email\exception log\October Errors.xlsx, the value for "This file location" using the original CELL function changes to the s:\email\exception log value in the table. The file on the shared drive is opened by a standard command in a macro.

Public Sub Increment_Master_File(sPath As String, sMaster As String, sMask As String, _
                            Optional iTopRowJunk As Integer = 1, _
                            Optional iBottomRowJunk As Integer = 0)
    
    Dim wkb As Workbook
    
    Const SOURCE_SHEET = "Sheet1"
    Const TARGET_SHEET = "Sheet1"
    
    Workbooks.Open sPath$ & sMaster$
    Set wkb = ActiveWorkbook
...
    wkb.Save
    wkb.Close

Having run the program to consolidate the various error log spreadsheets into a single file I then want to run queries that use the ERP Source location value, but since it's based on the workbook file location it now shows "s:\email\exception log\Sources" as the file location. I have a small function (thanks Chris Webb!) to get a dynamic file location from a table.

(PName as text) =>
let
    ParamSource = Excel.CurrentWorkbook(){[Name="tblParameter"]}[Content],
    ParamRow=Table.SelectRows(ParamSource, each ([ParamName]=PName)),
    ParamValue = if Table.IsEmpty(ParamRow) = true then null else Record.Field(ParamRow{0}, "Value")
in
    ParamValue

But the value is wrong and the query can't find the source file.

 

[shg]

If $A$2 has the original formula ...

=LEFT(CELL("filename", A2), FIND("[", CELL("filename", A2), 1) - 1)