+ Reply to Thread
Results 1 to 6 of 6

Macro to check if folder contains excel files with names similar to This Workbook

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Question Macro to check if folder contains excel files with names similar to This Workbook

    I am looking for VBA code that would search a specific windows folder to see if there are any excel files (XLAMs to be precise) with a file name similar to This Workbook.

    For 'similar', read if the first 12 letters of the file name are the same. i.e. Left(ThisWorkbook.Name, 12)
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

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

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to check if folder contains excel files with names similar to This Workbook

    Perhaps this could work?

    Please Login or Register  to view this content.
    Macro is untested and sPath must be changed to suit your needs.

    Alf

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Macro to check if folder contains excel files with names similar to This Workbook

    Thank you Alf. This will help.

    I probably should have provided more detail when starting this thread. I have an Add-In that is used by various persons on a network. Occasionally I upload a new version of the XLAM and place in the same shared location. The users would rarely check the folder for updates so after a while their copy of the installed add-in is several versions old. I was thinking of having code in the Add-In on Workbook_Open, have it check if there is new version of the Add-In in the folder and if so, copy to the users Add-In folder, install & close the outdated version of the add-in. That way the end user would constantly be using the latest version without their input required.

    Here is the code I have so far:

    Please Login or Register  to view this content.
    Obviously I don't want the code to return TRUE if there is only one XLAM with a similar name in the shared folder that has exactly the same name as the current add-in. (i.e. there will be multiple XLAMs in this folder. Some of them will be versions of this add-in, others will be unrelated add-ins. For the similar name test, the folder may contain the latest version & 1-2 previous versions of this XLAM. In this case, it would return TRUE. However if there is only one XLAM found with the similar name test and this XLAM file name is exactly the same as the ThisWorkbook then it would need to return FALSE). Any suggestions?
    Last edited by mc84excel; 02-15-2013 at 02:47 AM. Reason: Clarify the XLAM similar name exclusion criteria

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macro to check if folder contains excel files with names similar to This Workbook

    Do you have a version number for your XLAM?

    Perhaps you could have a small excel file with the latest version number + the names of the XLAM file so when sub "Workbook_Open" fires it checks version number in excel file and compares it with "working file".

    If version number in "working file" less than excel file update if not do nothing.

    Alf

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Macro to check if folder contains excel files with names similar to This Workbook

    Quote Originally Posted by Alf View Post
    Do you have a version number for your XLAM?
    My fault for not being clear. Yes, each XLAM has a version number or build date. This is simply a few numbers on the end of the file name. e.g. SAMPLENAME_13.xlam, SAMPLENAME_15.xlam

    I could have the version number as a Name inside the workbook but this is quicker and works (providing someone doesn't change the file name!)

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Macro to check if folder contains excel files with names similar to This Workbook

    I realise that I am heading off the original thread topic so I am marking this thread as solved and starting a new thread to cover what I am trying to achieve: http://www.excelforum.com/excel-prog...rk-folder.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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