VBA help opening text file with 2 dates

[rmobley232]

I am trying to write a code to import a text file on a daily basis that will have the previous and current yyyymmdd the title of the file. Here is the code when I recorded the macro:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\br4rkm\Desktop\RI_Maint_20190318_20190319.txt", Destination:= _
Range("$A$2"))
.Name = "RI_Maint_20190318_20190319"


Here is how I am attempting to update the code, not having a lot of luck. Thanks so much for the assistance:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\br4rkm\Desktop\RI_Maint_" & (Format(Now(), "yyyymmdd")-1)"_ & " & Format(Now(), "yyyymmdd") & ".txt", Destination:= _
Range("$A$2"))

 

[JLGWhiz]

try - 365 instead of - 1, better yet, for the previous year use

Dim yr As Long
yr = Right(Date, 4) - 1
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\br4rkm\Desktop\RI_Maint_" & yr & (Format(Now(), "mmdd"))"_ & " & Format(Now(), "yyyymmdd") & ".txt", Destination:= _
Range("$A$2"))

the - 365 would not work on leap year.

 

[rmobley232]

It isn't the year that changes, just the day of the month. So in the example, the date range was Monday, March 18, 2019 to Tuesday, March 19, 2019. The following day would then reference Tuesday, March 19, 2019 through Wednesday, March 20, 2019. Hope this clears it up!

 

[rmobley232]

I can get this to work for the previous day:
"TEXT;C:\Users\br4rkm\Desktop\RI_Maint_" & Format(Now() - 1, "yyyymmdd") & ".txt"

& this to work for the current day:
"TEXT;C:\Users\br4rkm\Desktop\RI_Maint_" & Format(Now(), "yyyymmdd") & ".txt"

I just can not figure out for the life of me how to combine those to get the previous day & current day in the same line. Thanks again!

 

[JLGWhiz]

Something like this:

Dim prv As String, cur As String
[COLOR=#ff0000]prv[/COLOR] = Format(Date -1, "yyyymmdd")
[COLOR=#ff0000]cur[/COLOR] = Format(Date, "yyyymmdd")
"TEXT;C:\Users\br4rkm\Desktop\RI_Maint_" & [COLOR=#ff0000]prv[/COLOR]" & "_" & [COLOR=#ff0000]cur[/COLOR] & ".txt", Destination:= _
Range("$A$2"))