Excel Bits & Pieces

Workbook Information Functions (Part 1)

Written by

robertsilk

in

In previous financial models and Excel projects I have worked on, the basic template worksheet has often included a formula in cell A1 (or similar) which dynamically returns the worksheet name (as per the text in the worksheet tab) to display as the worksheet title. This means that if the worksheet name is updated, the worksheet title also automatically updates.

This is achieved using the CELL() function, which can be used to output the worksheet file name in a standardised format:

C:\Your\File\Path\[File_name.xlsx]Sheet_Name

The file path is always shown first, followed by the file name with file extension, enclosed within the square brackets (“[ ]”), and finally the worksheet name for the cell reference provided to the CELL() function.

Excel restricts the use of certain characters in a valid Excel file name and worksheet name, including the “[” and “]” characters. This means that the last “[” and “]” characters in the CELL() file name string can be reliably used to find separate out the worksheet name, file name and file path.

Note: There are very few restrictions on the characters that can be used in a valid file path with most modern operating systems (especially Mac OS), so you cannot rely on using the first instance of “[” or “]” characters as you would with a standard FIND() function call.

In a previous post about my development of a TextBetween() function, I showed examples of formulae to extract the file path, file name, and worksheet name from the CELL() function output, particularly using the newer TEXTAFTER() and TEXTBEFORE() functions (see screen shot below). Following this, I thought it might be a fun exercise to build a set of custom functions which a user could use to extract this useful workbook information dynamically and simply.

The Workbook Information functions module (RXL_WbInfo) is made up of three custom LAMBDA() functions. The rest of this post details how the functions work, and the next post (Workbook Information Functions (Part 2)) will explain how the Excel Labs Advanced Formula Environment (AFE) can be used to create a module of complimentary LAMBDA() functions.

Please note: As all of the Workbook Information functions use the CELL() function output as described above, the referenced Workbook must be saved first in order for the functions to work correctly.

All of the functions outlined below have been designed to look and feel like native Excel functions, including required and optional parameters as appropriate, as well including input validation checks, error handling, and custom error messages. The code displayed in this post has been made to be more easily read and understood with new lines, additional spacing, indentation, and code commenting.

The version of the Workbook Information functions included here use custom error messages rather than standard Excel error codes (“# values”). I felt that as these functions are less likely to be used in any crucial calculations and are more likely to be used mainly for dynamic display of Workbook details on information worksheets or worksheet titles, this was a good opportunity to demonstrate using such custom error messages. For further discussion on the topic of error messages in custom LAMBDA() functions please see my previous post.

A. SheetName()

The SheetName() function is the simplest of the three functions with a single input parameter required; a valid cell reference.

= LAMBDA(
  // Parameter definition
  cell_ref,
  // Main function body/calculations
  LET(
    // Check that input provided is as cell reference, otherwise create a custom error message
    _inpChk, IF( NOT(ISREF(cell_ref)), "#[Error: cell reference required]", ""),
    // Get the filename string using the native CELL() function and the cell_ref argument
    _fileName, CELL("filename", cell_ref),
    // Use the TEXTAFTER() function to look for the final "]" character in the filename string
    // If no matching "]" character is found then return an empty string
    _wsName, IF(_fileName <> "", TEXTAFTER( _fileName, "]", -1, , , "#"), "#[Error: Workbook must be saved]"),
    // Check for an error value or empty string and if found return a custom error message, otherwise return the worksheet name string
    _rtn, IF( OR( ISERROR(_wsName), _wsName = "#"), "#[Error reading Sheet name]", _wsName),
    // Final check to see if there was an input error, and if so return the custom error message
    IF(_inpChk <> "", _inpChk, _rtn)
  )
)

The function uses a LET() function after the parameter declarations (line 5), to enable staged calculations and additional temporary variables within the main LAMBDA() calculation.

The first part of the calculations is an input validation check (lines 6-7), which ensures that the cell_ref input provided is a valid cell reference.

Next the CELL() function is used to get the full file name string output in the _fileName variable (lines 8-9). Then, the TEXTAFTER() function is used to get text after the last “]” character (instance number -1), which based on the expected CELL() function output, will represent the worksheet name for the cell reference provided (lines 10-12).

If the _fileName variable is a blank string then this suggests that the Workbook for the cell reference provided is not saved, and so we return an appropriate error message.

The result of the TEXTAFTER() function call (as per the _wsName variable) is checked, and if an error has occurred then an appropriate error message is provided as the _rtn variable otherwise the extracted worksheet name is used (lines 13-14).

Finally, the function uses an IF() statement to check whether the input validation check has flagged (lines 15-16) in which case the created error message is returned; if not the _rtn variable is returned, having already undergone error handling during the calculation of the _rtn variable value.

B. WorkbookName()

As with the SheetName() function, the WorkbookName() function requires a single valid cell reference as the main input parameter (cell_ref) but also includes the remove_extension optional parameter to add additional functionality.

  • If the input is TRUE the file name without file type extension is returned (equivalent to the Workbook name), otherwise the full file name including the file type extension is returned.
= LAMBDA(
  // Parameter definitions
  cell_ref,
  [remove_extension],
  // Main function body/calculations
  LET(
    // Update the optional input to a default of False if omitted or a non-Boolean/non-numeric value is supplied
    _extChk, AND( NOT(ISLOGICAL(remove_extension)), NOT(ISNUMBER(remove_extension))),
    _fileExt, IF( 
      OR( ISOMITTED(remove_extension), _extChk), 
      FALSE,
      remove_extension
    ),
    // Check that the cell_ref input supplied is a valid cell reference
    _inpChk, IF( 
      NOT(ISREF(cell_ref)), 
      "#[Error: cell reference required]", 
      ""
    ),
    // Get the filename string using the native CELL() function and the cell_ref argument
    _fileName, CELL("filename", cell_ref),
    // Use a combination of the TEXTAFTER() and TEXTBEFORE() functions to get the workbook name from the filename string
    _strName, TEXTBEFORE( TEXTAFTER(_fileName, "[", -1, , , "#"), "]", -1, , , "#"),
    // Remove the file extension from the workbook name string as required
    _wbName, IF(
      _fileExt, 
      TEXTBEFORE(_strName, ".", -1, , , _strName), 
      _strName
    ),
    // Determine whether to return an error message or the workbook name string
    _rtn, IFS(
      _fileName = "", "#[Error: Workbook must be saved]",
      OR( ISERROR(_wbName), _wbName = "#"), "#[Error reading Workbook name]", 
      TRUE, _wbName
    ),
    // Finally determine whether to return an input error message or the return string
    IF(_inpChk <> "", _inpChk, _rtn)
  )
)

The function uses a LET() function after the parameter declarations (line 6), to enable staged calculations and additional temporary variables within the main LAMBDA() calculation.

First, the function deals with the optional remove_extension input parameter (lines 7-13) and sets a default value of FALSE if no input value is provided by the user or an invalid input value is provided.

Next, the function performs input validation checks (lines 14-19) via the _inpChk variable, which check that the cell_ref input provided is a valid cell reference as required by the CELL() function.

The function uses the CELL() function to get the full file name output for the referenced cell (_fileName variable; lines 20-21), and then a combination of nested TEXTAFTER() and TEXTBEFORE() functions to extract the referenced Workbook file name (_strName variable; lines 22-23). As previously described, the last “[” and “]” characters (delimiter instance -1) are used as for this step as these are invalid characters for the Excel file name and Worksheet name and so will only be seen encompassing the Workbook file name in the expected CELL() function output.

If the user has chosen to use the remove_extension optional parameter, the _wbName variable will either the return the full text of the _strName variable (including the file extension) or use the TEXTBEFORE() function to return the text up to the last “.” character in the _strName text (lines 24-29).

The _rtn variable calculations provide error handling for the main calculations (lines 30-35). If the _fileName variable from the CELL() function output is a blank string this suggests that the referenced Workbook is not saved and so an appropriate error message is provided. If either the “[” or “]” characters were not found as expected in the previous steps, then an appropriate, more general error message is provided.

The final step in the calculations (lines 36-37) checks whether an input check was flagged and returns the created error message, otherwise the error handled _rtn variable value is returned.

C. FilePath()

The FilePath() function is the most complex and flexible of the three functions. It again requires a valid cell reference for the main cell_ref input parameter, and provides two optional parameters:

  • remove_las_char = if the input is TRUE, then the full file path is returned without the trailing “\” or “/” character
  • inc_file_name = if the input is TRUE, then the full file path is returned along with the file name including the file type extension
= LAMBDA(
  // Parameter definitions
  cell_ref,
  [remove_last_char],
  [inc_file_name],
  // Main function body/calculations
  LET(
    // Input checks
    _chrChk, AND( NOT(ISOMITTED(remove_last_char)), NOT(ISLOGICAL(remove_last_char)), NOT(ISNUMBER(remove_last_char))),
    _fnmChk, AND( NOT(ISOMITTED(inc_file_name)), NOT(ISLOGICAL(inc_file_name)), NOT(ISNUMBER(inc_file_name))),
    _refChk, NOT(ISREF(cell_ref)),
    // Dealing with optional parameters, setting default values if omitted
    _lastChr, IF(
      OR( ISOMITTED(remove_last_char), _chrChk),
      FALSE,
      remove_last_char
    ),
    _incFile, IF(
      OR( ISOMITTED(inc_file_name), _fnmChk),
      FALSE,
      inc_file_name
    ),
    // Get the full file path and name
    _full, CELL("filename", cell_ref),
    // Extract the file path only
    _path, TEXTBEFORE(_full, "[", -1, , , "#"),
    _filePath, IF(_lastChr,
      LEFT(_path, LEN(_path) - 1),
      _path
    ),
    // Extract the Workbook name element inc. file extension
    _wbName, TEXTBEFORE( TEXTAFTER(_full, "[", -1, , , "#"), "]", -1, , , "#"),
    // Check for input errors and calculation errors, and set the appropriate error message
    // Note - errors placed in order of importance/reporting as only a single message is returned for multiple errors
    _errChk, IFS(
      _refChk, "#[Error: cell reference required]",
      _full = "", "#[Error: Workbook must be saved]",
      OR(_chrChk, _fnmChk), "#[Error: invalid optional input]",
      OR( ISERROR(_filePath), _filePath = "#"), "#[Error reading File Path]",
      AND( OR( ISERROR(_wbName), _wbName = "#"), _incFile), "#[Error reading Workbook Name]",
      TRUE, ""
    ),
    // Construct the required output string
    _rtn, IF(_incFile,
      _path & _wbName,
      _filePath
    ),
    // Final check - if an error message is present return that, otherwise return the constructed output string
    IF(_errChk <> "", _errChk, _rtn)
  )
)

The function uses a LET() function after the parameter declarations (line 7), to enable staged calculations and additional temporary variables within the main LAMBDA() calculation.

First, the function performs input validation checks (lines 8-11) to ensure that any inputs provided are valid. After this the function deals with the optional parameters remove_last_char and inc_file_name, and assigns default values of FALSE if either is omitted by the user (lines 12-22).

Next, the function uses the CELL() function to get the full file path for the referenced cell (lines 23-26), and then uses the TEXTBEFORE() function to extract the text before the last instance of the “[” character (delimiter instance number -1). Based on the expected output format of the CELL() function, the final character before the last instance of the “[” character will either be a “\” or “/” character, depending on the referenced file location. As there may be circumstances where the user wants to provide the file path as the folder description only, there is the option to remove this trailing character using the remove_last_char parameter (lines 27-30).

The Workbook name is also extracted from the CELL() function output (_fileName) and stored as the _wbName variable (lines 31-32).

Next, error handling is performed to check if any of the input validation checks have failed or if an error has occurred during the preceding main calculations (lines 33-42). This is achieved using an IFS() statement, where the more important errors will trigger first and so the single custom error message will be provided in a hierarchical manner – i.e. invalid inputs come before calculation errors in the reporting to the user for debugging purposes.

After this the required output string is constructed and stored in the _rtn variable (lines 43-47). If the user has chosen to use the inc_file_name optional parameter, then the full file path (including trailing character) is provided (_path variable) with the full file name and file type extension (_wbName variable); otherwise, the adjusted file path string is used (_filePath variable).

Lastly, an IF() statement is used to check if an appropriate error message was returned from lines 35-42 (_errChk variable), and if so this is returned to the user as the function output; otherwise the calculated string in the _rtn variable is returned (lines 48-49).

If you want to see further information on the development and documentation of the above three functions then I have provided an Excel file (RXL_WbInfo.xlsx) which you can download and explore at the following GitHub repository:

https://github.com/r-silk/RXL_Lambdas

The file contains a page on each of the function with the final versions of the functions as seen above as well as version of the functions which do not use the newer TEXTBEFORE() and TEXTAFTER() functions and instead demonstrate how the same functionality can be achieved with older Excel functions and approaches. There is also a “Demo” worksheet which demonstrates the outputs from each function using the main input parameter combinations.

The file contains a copy of each function in the Defined Names manager which can be copied into your own file, or alternatively there is a “minimised” version of each function which can be copied and pasted directly into the Name Manager when defining a new name in your own files — please use the suggested function names when using this method:

  • RXL_SheetName()
  • RXL_WorkbookName()
  • RXL_FilePath()

Finally, there Gist URLs for each of the functions, however, I would encourage you to read the next post (Workbook Information Functions (Part 2)) and use the Gist URL for the RXL_WbInfo module instead if you are intending on adding these functions to an Excel file via the Excel Labs Advanced Formula Environment (AFE).

Comments

Leave a comment

More posts