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

[absolum101]

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>

 

[AlphaFrog]

You may be missing just the path separators

[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]

 

[absolum101]

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.

 

[AlphaFrog]

[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?

 

[absolum101]

No, that was a typo. Rpt is the same as report.

 

[AlphaFrog]

It makes it a bit of a challenge to diagnose code with typos.

Did you retype the code in this thread?

 

[absolum101]

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.

 

[absolum101]

I take my

fPath

and add the

fName

because it will be located in a subfolder. But it won't pass to the

Report

variable.
I.E. fPath = "D:\Users"
fName = Format(d, "mmddyyyy")
Report = Dir(fPath & "name of report" & fName(Date) & "_*")

 

[AlphaFrog]

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?

 

[absolum101]

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.