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>
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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:

absolum101

New Member
Joined
Feb 6, 2018
Messages
11
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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:

absolum101

New Member
Joined
Feb 6, 2018
Messages
11
No, that was a typo. Rpt is the same as report.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
It makes it a bit of a challenge to diagnose code with typos.

Did you retype the code in this thread?
 

absolum101

New Member
Joined
Feb 6, 2018
Messages
11
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

New Member
Joined
Feb 6, 2018
Messages
11
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) & "_*")
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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

New Member
Joined
Feb 6, 2018
Messages
11
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.
 

Forum statistics

Threads
1,082,155
Messages
5,363,468
Members
400,739
Latest member
Siopsy

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top