+ Reply to Thread
Results 1 to 18 of 18

Code to create a list of my worksheet workbook names

  1. #1
    Registered User
    Join Date
    01-09-2015
    Location
    Illinois, USA
    MS-Off Ver
    2013
    Posts
    63

    Code to create a list of my worksheet workbook names

    I would like to create a code that will populate a list available in a dropdown box to be able to select an individual workbook, open it, and if wanted to print the sheet.

    The list would need to expand as there will be new workbooks added daily.

    Thanks in advance for help on this item

    Dennis

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Code to create a list of my worksheet workbook names

    SO in your workbook you would have:
    1. a hidden "LIST" sheet where these workbook names need to be stored, list in column A
    2. a dynamic named range formula that always encompasses all the item listed in column A, no matter how many there are
    3. a cell on the LIST sheet listing the PATH to the folder where you want to list all the files found there.


    When the workbook opens, the first thing it does secretly is secretly rebuild that column A listing, then everything else operates the same as normal drop down validation list.

    Sound correct?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-09-2015
    Location
    Illinois, USA
    MS-Off Ver
    2013
    Posts
    63

    Re: Code to create a list of my worksheet workbook names

    Yes, sounds correct. I would appreciate that very much Sir.

    Dennis

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Code to create a list of my worksheet workbook names

    Based on the parameters in post #2, this macro will create a list of files on the LIST sheet in column A using the path stored in B1 of that sheet.
    The list can be used with a dynamic named range called MyFiles that lists everything in column A at all times.

    The attached workbook shows it working. Here is the code in the ThisWorkbook module that updated the list each time the workbook is opened.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by JBeaucaire; 03-28-2015 at 08:47 PM.

  5. #5
    Registered User
    Join Date
    01-09-2015
    Location
    Illinois, USA
    MS-Off Ver
    2013
    Posts
    63

    Re: Code to create a list of my worksheet workbook names

    Thanks much,

    I am confused on how to enter this in my file to work ?
    Can you tell me what parts of the code I need to change to reference your code to work ?

    Dennis

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Code to create a list of my worksheet workbook names

    Heh, I thought it was pretty clear.

    1) Create a sheet called LIST in your workbook
    2) List the source folder in B1. (could just copy my LIST sheet into your workbook)
    3) Copy the code in ThisWorkbook module into your ThisWorkbook module, or copy and paste from the post above.

  7. #7
    Registered User
    Join Date
    01-09-2015
    Location
    Illinois, USA
    MS-Off Ver
    2013
    Posts
    63

    Re: Code to create a list of my worksheet workbook names

    I have Created a File tab
    Entered the code
    Created a button to open the list

    Pop up ask to update list, I select yes and get this error code

    MsgBox "No files were found in the given path: '" & fPATH & "', the list is empty"[/B][/U][/U][/B]

    My file is located on C:\Desktop\Trucking Dispatch

    No list is populating ??

    Thanks again for the great help

    Dennis

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Code to create a list of my worksheet workbook names

    I would suspect that path isn't correct. On my system for instance the path to the "desktop" is actually:

    C:\Users\Jerry\Desktop\

  9. #9
    Forum Contributor
    Join Date
    03-05-2009
    Location
    usa
    MS-Off Ver
    Excel 2016 32Bit
    Posts
    1,173

    Re: Code to create a list of my worksheet workbook names

    I tested JBeaucaire code and runs Perfect. dwheggen1 make sure you have the path on C drive.Go on C drive and make folder temp, put your workbooks there
    and all will work.

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Code to create a list of my worksheet workbook names

    BTW I misspelled the macro name, it should have been Workbook_Open

  11. #11
    Registered User
    Join Date
    01-09-2015
    Location
    Illinois, USA
    MS-Off Ver
    2013
    Posts
    63

    Re: Code to create a list of my worksheet workbook names

    OK, fixed location, made temp folder, placed my worksheet file in it.
    The file now shows up in the list in A:1, however I was wanting the list to show workbooks within my work sheet,
    not files in a directory ??

    Can this be done ?

    Regards, Dennis

  12. #12
    Forum Contributor
    Join Date
    03-05-2009
    Location
    usa
    MS-Off Ver
    Excel 2016 32Bit
    Posts
    1,173

    Re: Code to create a list of my worksheet workbook names

    Workbook in worksheets? Do you mean WorkSheets in a WorkBook?

  13. #13
    Registered User
    Join Date
    01-09-2015
    Location
    Illinois, USA
    MS-Off Ver
    2013
    Posts
    63

    Re: Code to create a list of my worksheet workbook names

    Sorry, Yes I mean WorkSheets in a WorkBook?

    Then I could select any sheet from a dropdown list to preview or print if needed

    Thanks much

  14. #14
    Forum Contributor
    Join Date
    03-05-2009
    Location
    usa
    MS-Off Ver
    Excel 2016 32Bit
    Posts
    1,173

    Re: Code to create a list of my worksheet workbook names

    Something like this?
    Copy code to worksheet1, make couple sheets to see results

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    01-09-2015
    Location
    Illinois, USA
    MS-Off Ver
    2013
    Posts
    63

    Re: Code to create a list of my worksheet workbook names

    Sorry, ?? I am not understanding how to entr

    I already have workbook named "Trucking orders"
    I have a work Sheet named "List"
    I wish to have the list of "Sheet Names" to be populated in a column in the "List" Sheet in Column A:1
    The directory of the "Trucking Oder file" is in Cell B:1

    Not sure where to copy your code as sent ?
    Do I then need to make a button or macro to execute the code or will it execute and update automatically as workbooks are added ?

    Regard's
    Dennis

  16. #16
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Code to create a list of my worksheet workbook names

    Well this is certainly different from the original question. Ok, no special folder needed to be listed in B1. Replace the ThisWorkbook code with this:

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    01-09-2015
    Location
    Illinois, USA
    MS-Off Ver
    2013
    Posts
    63

    Re: Code to create a list of my worksheet workbook names

    Sorry if I misstated or confused my original statements. The latest works great !
    Thanks so much
    Dennis

  18. #18
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Code to create a list of my worksheet workbook names

    As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
    FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code to List all worksheet names (tab names)
    By howard101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2007, 12:51 PM
  2. Create a list in one worksheet of the other worksheets' names
    By Kelli in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  3. [SOLVED] Create a list in one worksheet of the other worksheets' names
    By Kelli in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  4. Create a list in one worksheet of the other worksheets' names
    By Kelli in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. Create a list in one worksheet of the other worksheets' names
    By Kelli in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-07-2005, 04:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1