Importing Multiple .Txt Files Into Excel Based on the Date Last Modified in VBA (NEW)

  • 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

    Hello everyone,

    I'd appreciate some coding help in Excel using VBA. I am new to this.



    Goal of project:

    1. The purpose of this code is the pull all of the files located the file name myPath="_____".

    2. List out all the lines in the .txt files in seperate columns (a new row is created after each text file)

    3. The goal is to be able to only pull files that have a 'lastModifiedDate' that are within a desired range (between 'Start Date' and 'End Date')

    This is the purpose of the If statement. The contents of the .txt file should be saved in a unique row, with each line separated into columns. If the 'lastDateModified' is outside the range,

    as applied in the Else statement, then it moves to the next .txt file.


    Error: "Run-time error '55': File already open."


    Note: I had success with the code without the date criteria. Everything was the same with the code except for the for and else loop. I'm assuming there is some sort of problem with the 'Close #1' statements but I am unsure...

    The data in excel shows what I got from the text files without the date criteria. It takes a very long time to load ALL of the text files about (17,000 files spanning from 10 years ago). I want to be able to update my excel file possibly on a daily basis to import the new .txt files that are being created.


    Thank you very much for any help.



    Code:



    Picture 1: .txt files that are being imported


    Picture 2: Example of one of the .txt files


    Picture 3: All of the .txt files that were successfully imported to my excel file, but it took a long time and makes the file lag. I don't care about data from many years ago. I want to be able to only pull .txt files based on the date the file was last modified.


    (NOTE: I can't get the pictures to format correctly so you'll have to match them yourself ;)

  • Welcome to Ozgrid Forum


    You need to read the Forum Rules to understand how to use the Forum.


    I will add code tags this time. After you have read the rules attach an example workbook and a sample of the text file, we cannot work with images.

  • This question was posted on 4 different sites:


    Oz Grid: Importing Multiple .Txt Files Into Excel Based on the Date Last Modified in VBA (NEW)

    -No response (09/22/20)


    VBAexpress: http://www.vbaexpress.com/foru…n-VBA&p=404213#post404213

    -No response (09/22/20)


    MrExcel: https://www.mrexcel.com/board/…ast-modified-vba.1145236/

    -Response by John_W provided code that works but no correction to my code (09/09/20)


    Excel Forum: https://www.excelforum.com/exc…te-last-modified-vba.html

    -No response (09/22/20)