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

Hello everyone,

I'd appreciate some coding help in Excel using VBA.


Background: There is a new .txt file made for each furnace run. The .txt file is created automatically in the system, and goes unedited by anyone, therefore using the date last modified is a viable way to sort through the files.

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 its unique role, seperated 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:

Please Login or Register  to view this content.

----------------------




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 )