CODEWITHSUNDEEP
excelvba
sTan_The_mAn
sTan_The_mAn

Reputation: 9

How to search for filename, that changes each day because it includes the date in its name, within date range put into cells on worksheet

I am able to read and write the current day's file to a text file for ease of input to sheet. But past day's reports with the same name differ in that they include the date in the name i.e. C:\Users\name\reports\report.html vs C:\Users\name\reports\archive\date\report.html I can't figure out how to tell it to go through dates selected. Additionally the report date shows as 06292019 not 06/29/2019.

I haven't tried much, as I don't really know where to start. Most of the questions I have browsed don't apply specifically enough.

Sub FindoldFile()

Dim fName As String
Dim fPath As String
Dim Rpt As String
Dim Source As String
Dim filePath As String
Dim Textfile As Integer
Dim Ifile As String
Dim CurRow As Long

fPath = "C:\Users\name\reports\"
Rpt = Dir(fPath & "reportname" & "date" & "_*")
'Would like to have "date" looped through date range input on sheet in cells

If Rpt = "" Then
    MsgBox "No report found"
    Exit Sub
End If

Source = fPath And Rpt
filePath = "C:\Data\report.txt"
Textfile = FreeFile
Open filePath For Output As Textfile 'First one as for output, second as for append?
Close Textfile
Kill "C:\Data\report.txt"
Ifile = "C:\Data\report.txt"
FileCopy Source, Ifile
Source = Ifile

'Here I would want to input multiple files from the date range selected
CurRow = 2
Open Source For Input As #1
    Do While (Not EOF())
        'do stuff
        CurRow = CurRow + 1
    Loop
Close #1
End Sub

I want to be able to put an start and end date values in two cells, then write all of them to .txt. Following that, input them to excel sheet.

Edit, after looking at the thread suggested by Siddharth, I have come up with this adaptation. Of which I will test out when I get back to work tomorrow and update after.

Sub FindOld()

Dim ws As Worksheet
Dim st As Range
Dim en As Range
Dim x As Integer
Dim stDate As Date
Dim enDate As Date
Dim d As Date
Dim LR As Long
Dim CurRow As Long
Dim wb As Workbook
Dim fPath As String
Dim fName As String

Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Activate
fPath = "C:\Users\"

Dim LastRow As Long
With ActiveSheet
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        LastRow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        LastRow = 1
    End If
    LR = LastRow
End With

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

Exit Sub

End Sub

Upvotes: 0

Views: 66

Answers (1)

SamP
SamP

Reputation: 176

I'm going to assume where you use the word date you want the text such as "06292019" and for that date to be worked out in the loop. In that case DoStuff should be something like:

if IsDate(sht.cells(CurRow, 1)) then fDate = format(sht.cells(CurRow, 1), "mmddyyyy") ' Do stuff end if

I am assuming you have a variable sht mapped to the relevant worksheet.

That loop will work if you have a range of dates in the rows. I you want to loop from one date to another, as you also mentioned, you'll want to create a loop on variable i set to 0, and then run the loop using (firstDate + i) as the loop date, and stop when (firstDate + i) is greater than lastDate.

Upvotes: 1

Related Questions