none
GET.WORKBOOK(1)&T(NOW()) formula RRS feed

  • General discussion

  • Hi everyone,

    I just learned that by writing this magical word GET.WORKBOOK(1)&T(NOW()) in reference to name range and the formula below in any cell range I can get a list of sheet names. But I don't understand any of these things. How does that work? How does this formula return the sheet names? Also when I was given this formula in a forum I was told to save the file as a macro-enabled file. Why? But the following formula worked fine without having to save it as a macro-enabled file.   FERROR(INDEX(MID(SheetNames;FIND("]";SheetNames)+1;255);ROWS(A$1:A1));"")
    Friday, July 29, 2016 1:14 PM

All replies

  • Re:  Get.Workbook( )

    That is an Excel 4 function.  Excel 4 used macro sheets instead of modules.

    The syntax is:  Get.Workbook(type_num, name_text)
      "type_num" 1 returns the names of all documents in the workbook as an horizontal array.
      "name_text" is the name of the workbook (if omitted, the active workbook is used)

    I am unsure if all (or any) XL4 functions still work in XL2016 - MS keeps making changes to Excel and calling them "improvements".

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)


    • Edited by James Cone Thursday, February 2, 2017 1:30 PM Update Link
    Saturday, July 30, 2016 2:05 AM
  • The XLM macros are very helpful.....you can download help file from below link..

    https://support.microsoft.com/en-us/kb/128185

    "The following file is available for download from the Microsoft Download Center:

    "


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, July 30, 2016 3:29 AM
    Answerer
  • This a an excellent function and a bit complex. Shall try to explain below....

    Only 3 funtions is essential below and you need to take a grip. Rest are easy if you understand those 3 functions.

    ******************

    GET.WORKBOOK is XLM macro and it returns various information on Workbook. It takes two argument.

    GET.WORKBOOK(type_num, name_text)

    The type_num refers to various properties of workbook. In our case it is 1 . That means we want sheet names. If we give 4 it will return total number of sheet in workbook. You can download above link and see what other properties we can get.

    name_text is name of open workbook. If omitted then active workbook is assumed.

    So here it returns an Horizontal array of Sheet names. Like below...

    {"[Book1.xlsb]Sheet1","[Book1.xlsb]Sheet2","[Book1.xlsb]Sheet3"}

    *********************************

    ROWS return number of rows in range. It takes cell address as argument. Here ROWS(A$1:A1)) used. So if you paste in excel cell say E2 it will return 1 as in A$1:A1 only 1 row available. If you copy the cell E2 to bottom cell E3,E4,E5 excell will adjust the cells address like ROWS(A$1:A2)),ROWS(A$1:A3)),ROWS(A$1:A4)). As the address is given as A$1:A1, the first A$1 is absolute address so excel does not adjust it. So in E3 we shall get 2 for formula ROWS(A$1:A2)). As A$1:A2 has 2 rows. In E4 get 3, in E5, get 4.

    **********************************

    Last is Index function

    INDEX(array,row_num,[column_num])

    Index functions returns a value from a given array specified by row_num and Column_name. Column_name is optional and assumed 1 if omitted.

    INDEX(A1:A10,2) will return value at row 2 that means A2.

    **********************************

    At very bare level copy below in E2. And then copy E2 and paste. If you paste less some sheet will not be returned and if you paste more some cell will return error.

    =INDEX(SheetNames;ROWS(A$1:A1))

    After adjusting excel the formulas will be like below in E3,E4,E5

    =INDEX(SheetNames;ROWS(A$1:A2))

    =INDEX(SheetNames;ROWS(A$1:A3))

    =INDEX(SheetNames;ROWS(A$1:A4))

    Excel will evaluate the ROWS...like explained above. Intermediate result will be like below.

    =INDEX(SheetNames;1)

    =INDEX(SheetNames;2)

    =INDEX(SheetNames;3)

    =INDEX(SheetNames;4)

    Finally Index function will return 1st,2nd,3rd,4th item of SheetNames in E2,E3,E4,E5 consecuatively. And GET.WOrkbook has returned below in SheetNames name.

    {"[Book1.xlsb]Sheet1","[Book1.xlsb]Sheet2","[Book1.xlsb]Sheet3"}

    So ultimately we the sheet name one by one

    *****************************************************

    Now let me go through rest functions which polishes the output.

    You will note that the output contains book name also. Not Sheet name only. Instead of using SheetNames  directly in INDEX function we use below underlined portion..

    INDEX(MID(SheetNames;FIND("]";SheetNames)+1;255);ROWS(A$1:A1))

    so that INDEX function get below

    {"Sheet1","Sheet2","Sheet3"} and output is only sheet name

    ************************************

    Let's see how it happens...

    FIND function returns the position of a text within a text.

    FIND("]";SheetNames). This returns position of ] which separates sheet name and filename

    FIND("]", {"[Book1.xlsb]Sheet1","[Book1.xlsb]Sheet2","[Book1.xlsb]Sheet3"}) will return position of ]. So it returns 12,12,12.

    After evaluating FIND we get below..

    MID(SheetNames;12+1,12+1,12+1;255)

    MID returns a portion of Text as specified. The first argument is text, 2nd argument is the position from which extraction starts and 3rd is number of character to extract.

    MID("ABCDE",3,2) will return CD.

    Here 255 given so that maximum possible sheet name can be covered. So MID starts extracting from character after "]" and continues till 255th.

    So output of MID(SheetNames;FIND("]";SheetNames)+1;255) is {"Sheet1","Sheet2","Sheet3"} which is fed to INDEX.

    **********************************

    IFERROR takes two argument. If result of 1st argument is false it return 2nd argument else 1st argument is retrurned.

    =IFERROR(INDEX(MID(SheetNames;FIND("]";SheetNames)+1;255);ROWS(A$1:A1));"")

    If we used formula in more cell than sheet name the above underlined (1st Argument) will return #REF so IfERROR will return "" (2nd Argument). If 1st argument is OK then 1st argument is returned.

    ******************************

    NOW() returns current TIME and T checks if it's argument is text or not. If text then that it returned else "" is returned. It seems it is not needed here.





    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, October 12, 2016 10:17 AM
    Answerer
  • thanks you for explaining, it is necessary for my study

    Saturday, August 25, 2018 4:23 PM