none
Batch Script: Creation of Properly Named Folder and Contained Files, Running of Excel Macro RRS feed

  • Question

  • Hello,

     

    I am wondering if a Batch script is capable of a process that I want to automate in a shared folder. Roughly, it is as follows.

    1.   Batch script is opened in a folder that contains other folders named "Mmm yyyy Data" (e.g. Dec 2018 Data, Jan 2019 Data, Feb 2019 Data)

    2.   Determines the latest month present in the folder based on this naming convention.

    3.   Increments that, saves the value to “new_month_year” in the form Mmm yyyy and displays a prompt “Do you want to create a folder and files for “[new_month_year] Data”? Y/N?” If Y, program skips to 6. If N, program continues to 4.

    4.   Prompt is displayed, “Enter your desired month and year in the form Mmm yyyy.” User enters month and year in the form Mmm yyyy; this overwrites the “new_month_year” variable.

    5.   Prompt is displayed, “Do you want to create a folder and files for “[new_month_year] Data”? Y/N?” If Y, program continues to 6. If N, program skips to 9.

    6.   A new folder named “new_month_year Data” is created in the current directory.

    7.   User selects old folder from list of folders in current directory.

    8.   For each Excel file within the old folder, place in new folder with these edits:

    a)   (psuedo-code from Excel .. see below for definition of x) new file name = left(x) & new_month_year

    b)   new file content = old file content

    c)   executes 'file contents update' macro in Excel [still to be coded]

    d)   moves to next file in old folder until all have been processed

    9.   Displays the message, “[count of files processed] files have been copied, renamed for “new_month_year”, and updated in Excel. Press any key to exit.”

    10.        Reads a key press as command to exit.

     

    Excel file name facts:

    ·         Examples: "blah blah blah Dec 2018.xlsx" "blah blah blah Jan 2019.xlsx" "blah blah blah Feb 2019.xlsx"

    ·         The only thing changing is the three character month and four digit year.

    ·         That's eight characters when you include the space between these.

    ·         These always occur at the end of the string.

    ·         So take the length of the original name and subtract 8 to get number of characters to take from the beginning of original file name for the new file name, variable x mentioned in 8a above.

     

    I’ve been poking around to learn how to write Batch scripts for this purpose; but this problem is an advanced application for me. If folks can help me put this together, I'd greatly appreciate it.

    Warm regards,

    Alexa


    • Edited by Alexa_T Tuesday, April 23, 2019 6:24 PM edit for post clarity
    Tuesday, April 23, 2019 6:22 PM

Answers

All replies