Open Multiple XLSM files from a given Folder

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    https://chandoo.org/forum/thre…rom-a-given-folder.46434/

    https://www.excelforum.com/exc…n-folder.html#post5527072


    Hi Guy's,

    I am new to this forum.


    I have multiple XLSM files in a folder, which run the macro as soon the file is opened.


    I need to run these multiple files so that First file is opened, macro runs, saves and close, then the next file is opened and so on.

    All these files are in one folder.

  • A bit more information needed.


    Is an identical macro run in all the files?

    If so, then is that macro already in each of the files?


    That will do for a start, further information may be needed depending on your replies to the above.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • In that case it would be far simpler to have the macro in you Personal Workbook together with code that will open each file in the folder, run the code you already have, then save the file and close it.


    Can you attach a sample file complete with the code that you already have.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I have looked through your code, I see that you select various parts of the URL in order to import the required data,

    will the selection be the same for every file?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Quote

    I have multiple XLSM files in a folder, which run the macro as soon the file is opened.

    Do you mean each file has a Workbook_Open event code which calls the "HistoricalData" procedure?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • OK


    No need to stop the Workbook_Open code


    Doing a code for you to test now.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Ignore my last reply! The Workbook_Open code does need to be stopped.


    Instructions:

    1. Copy this code into a new Module in your Personal Workbook

    2. Copy JUST the 2 Functions from one of your files to the same Module

    3. Remove existing code from each file (including the Workbook_Open code). If these are the only codes in each file

    then you can change them to .xlsx instead of .xlsm

    4. Run "UpdateAllFiles"

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Note that there is no need to include quotation marks to the InputBox entries, but each entry must be cntered and separated by a comma and a space.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Did you include a back-slash at the end of the folder path?


    If so, then please attach your file.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I sent you a PM in case your file contains sensitive data

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I am attaching 2 files


    The first ("FileConverter") will run through all your files in the folder and do the following:

    1. Remove the button

    2. Remove the Data Validation

    3. Save the file as .xlsx

    4. Completely delete the original .xlsm file


    This will just be a one-off run but should save you a ton of time rather than making those 4 changes on 400 files manually!


    The second is the Controller file I mentioned. Complete the required criteria in cells D5 to D9 (none can be left empty!) then click the button.


    The code will loop through all the files in a folder and update them according to the entered criteria. It will take a bit of time to update all 400 files, so you can go for a coffee break and leave it churning away! There will be a message when the code completes telling you how many files out of the total file count have been updated.


    I have tested various different criteria and all works except for "Dividends" & "Splits", but I notice that fails on the Sample file you sent.


    I have left your existing code much as it was, just change the indentation, and modified the variables where necessary. I also added a bit to the error message box so that it shows the file name where the error occurred.


    I suggest you copy 6 or so files to a new folder and run tests on those before running the code on all 400 files.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KJBox

    You are a Magician of VBA.

    The things worked so smoothly like Butter Knife.

    May all Mighty God Bless you.

    Thanks a Lot

    :love::love:<3<3