How to find and read multiple files based on date range.

absolum101

New Member
Joined
Feb 6, 2018
Messages
11
I have been searching for a way to be able to read and write multiple files to .txt. I can currently get the most current file with a wildcard, but the other archived files are in sub-folders named by date i.e. 07072019.

I would have two dates on a sheet, say B3 and C3. B3 being start date and C3 being end date.

I tried this, but I am not able to get the date(fName) to when dir function is performed. It will see the filepath and date, but not combine.
I can access by inputting manually or opening it from the window explorer. Is there an easier way of searching and building a .txt file from several dates worth of files?

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">For x = 0 To LR - 2

Set st = Range("B2").Offset(x, 0)
Set en = Range("C2").Offset(x, 0)
stDate
= DateSerial(Year(st), Month(st), Day(st))
enDate
= DateSerial(Year(en), Month(en), Day(en))

Dim subPath As String
Dim Report As String
LR
= LR + 1

For d = stDate To enDate
Debug
.Print d
fName
= Format(d, "mmddyyyy")
subPath
= fPath & fName
Report
= Dir(subPath & fName & "_*" & ".html")

Source
= subPath And Rpt
filePath
= "C:\Data\report.txt"
Textfile
= FreeFile

If d = stDate Then
Open filePath
For Output As Textfile
Close Textfile
Kill
"C:\Data\report.txt"
Ifile
= "C:\Data\report.txt"
FileCopy Source
, Ifile
Source
= Ifile
Else
Open filePath
For Append As Textfile
Close Textfile
Ifile
= "C:\Data\report.txt"
FileCopy Source
, Ifile
Source
= Ifile
End If
Next d
Next

CurRow
= 2
Open Source
For Input As #1
Do While (Not EOF())
'do stuff
CurRow
= CurRow + 1
Loop
Close
#1



</code>
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
You may be missing just the path separators

Code:
[COLOR=#101094][FONT=Consolas][FONT=inherit]For[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] d [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] stDate [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]To[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] enDate
Debug[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Print d
fName [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Format[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]d[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit],[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"mmddyyyy"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
subPath [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] fPath [/FONT][/FONT][/COLOR][COLOR=#ff0000][FONT=Consolas][FONT=inherit]&[/FONT][/FONT][FONT=Consolas][FONT=inherit] "\" & [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]fName [/FONT][/FONT][/COLOR][COLOR=#FF0000][FONT=Consolas][FONT=inherit]&[/FONT][/FONT][/COLOR][COLOR=#FF0000][FONT=Consolas][FONT=inherit] "\"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
Report [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Dir[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]subPath [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]&[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] fName [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]& [/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"_*" [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]& [/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]".html"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR]
 
Last edited:
Upvote 0
I included separators in the address, I just didn't list it for privacy. It will show correctly as fName, but for some reason it won't transfer via dir so to pass it to Report.
 
Upvote 0
Code:
[COLOR=#ff0000][FONT=Consolas][FONT=inherit]Report [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Dir[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]subPath [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]&[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] fName [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]&[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"_*"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]&[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]".html"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]

Source [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] subPath [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]And [/FONT][/FONT][/COLOR][COLOR=#ff0000][FONT=Consolas][FONT=inherit]Rpt[/FONT][/FONT][/COLOR]

You use the two string variables Report and Rpt. Is that what you really want?
 
Last edited:
Upvote 0
It makes it a bit of a challenge to diagnose code with typos.

Did you retype the code in this thread?
 
Upvote 0
I edited some information out.
I just left the basis of what I am trying to accomplish, or to find a better way as I don't know if this approach is even the best way.
 
Upvote 0
I take my
Code:
fPath
and add the
Code:
fName
because it will be located in a subfolder. But it won't pass to the
Code:
Report
variable.
I.E. fPath = "D:\Users"
fName = Format(d, "mmddyyyy")
Report = Dir(fPath & "name of report" & fName(Date) & "_*")
 
Upvote 0
It should work if you have correct path separators in there and the full pathname is correct. You say you have the separators, but your code doesn't show them. I have to take your word they're there and correct.

Trt to...

Debug.Print fPath & "name of report" & fName(Date) & "_*"

Then copy the result from the Immediate window into a file browser. Does it take you to the file?
 
Upvote 0
Sooo...kind of embarrassed. I was adding one of the separators during the Dir function, which I guess you can't do, because it was not counting it. My apologies.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top