Importing multiple .txt files having the current date in the filename using VBA

[shubhamsinghrathore]

Hi there,

with respect to one of my process automation, can anyone help me with the VBA code to use multiple latest .txt files imported in a single sheet in a workbook.
Following are the requirements that are needed to get this automated:-

1. Every Monday 5 different files with the name "Client+Delivery+Schedule+1+(primary)YYYYMMDD.txt" are generated, they all have the same name except the number after schedule changes in every one of them, from 1 to 5.
which would look like this:
Client+Delivery+Schedule+1+(primary)20220808
Client+Delivery+Schedule+2+(primary)20220808
Client+Delivery+Schedule+3+(primary)20220808
Client+Delivery+Schedule+4+(primary)20220808
Client+Delivery+Schedule+5+(primary)20220808

2. all of them have data starting with a header of name, ids, etc in the first row, so I need to copy the data from files 2 to 5 starting from the 2nd line and has to be posted on the same worksheet from where the previous file data ends.

3. the data, in the end, has to be exported in the .txt file only with the name "Client Delivery Schedule (primary)YYYYMMDD.txt"

since this is repetitive every Monday, hoping to get this automated.