+ Reply to Thread
Results 1 to 15 of 15

Command Button to retireive open workbook names

  1. #1
    Forum Contributor
    Join Date
    12-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    127

    Command Button to retireive open workbook names

    Not sure if its possible,

    I have a master workbook which logs all potential new business.

    It logs NAME, REP, COMPANY, PHONE, ACCOUNT MANAGER, POSTCODE & COMMENTS.

    It currently does this using INDIRECT functions.

    The information comes via emailed workbooks. I open the emailed workbook, then type the filename into A1 (or whatever row is the next available empty one)

    Then the INDIRECT functions populate the relevant cells. I then PASTE VALUES and save.

    I get about 15 emails aday, and combined, it takes up a fair amount of time.

    so,

    what im thinking is, If I save all the work until the end of the day, then open ALL files I have received,

    Would it be possible to create a macro, assign it to a command button on the master workbook, and when clicked,

    It systematically retrieves the filenames of all open workbooks, and paste them into column A one by one, populating the cells, then copy & paste values?



    How much work would be involved for a macro like this?


    thanks.

    Mike.
    Last edited by Robotacha2010; 05-25-2012 at 08:55 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Command Button to retireive open workbook names

    I think it is,

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Command Button to retireive open workbook names

    This returns the name of all open workbooks

    Please Login or Register  to view this content.
    Please take time to read the forum rules

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Command Button to retireive open workbook names

    @Steffen: wb.Name does not need to be in brackets although, in this case, it doesn't cause a problem.


    Regards, TMS

  5. #5
    Forum Contributor
    Join Date
    12-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    127

    Re: Command Button to retireive open workbook names

    Brilliant. Ok, so I know its relatively simple to obtain the name,

    im guessing, it will be along the lines of (bear in mind im an absolute novice with VBA)

    Please Login or Register  to view this content.
    obviously I dont need a message popup, I need to be able to paste the filename into the document.

    I tried the first code of debug.print but it didnt seem to do anything?

  6. #6
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Command Button to retireive open workbook names

    I know,

    Im just used to writing this way, calling msgbox as a function with brackets.

  7. #7
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Command Button to retireive open workbook names

    Yes.

    Please Login or Register  to view this content.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Command Button to retireive open workbook names

    To build on Steffen's answer, you will (of course) get the name of the workbook running the macro ... which you probably don't want.

    So:

    Please Login or Register  to view this content.

    Regards, TMS

  9. #9
    Forum Contributor
    Join Date
    12-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    127

    Re: Command Button to retireive open workbook names

    That is fantastic. Sincere thanks.

    only 1 more issue...

    it lists personal.xlsx & personal xlsb

    how do I exclude / prevent these from being pasted into column A?

    any ideas?

    again, sincere thanks for your help.!!

  10. #10
    Forum Contributor
    Join Date
    12-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    127

    Re: Command Button to retireive open workbook names

    just seen your previous reply.

    I will try to add these exclusions into the code you have provided.

    thankyou sir!

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Command Button to retireive open workbook names

    Same way as I excluded ThisWorkbook:

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    12-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    127

    Re: Command Button to retireive open workbook names

    told you im a novice

    how would I exclude the personal.xlsx and personal.xlsb files from being printed?

    you will probably laugh at this, but I tried;

    Please Login or Register  to view this content.

    and guess what? It never worked...

  13. #13
    Forum Contributor
    Join Date
    12-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    127

    Re: Command Button to retireive open workbook names

    You're a legend.

    rep added.

    thankyou very much squire!!!

  14. #14
    Forum Contributor
    Join Date
    12-04-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    127

    Re: Command Button to retireive open workbook names

    one more quick cheeky question,

    what if I wanted to paste the filename into column A as a hyperlink? (to the original file)

    Is this an easy one? or more indepth?

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Command Button to retireive open workbook names

    You're welcome. Thanks for the rep.

    I think the original question is answered.

    You should open a new thread for this new question.


    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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