VBA to Pull Data from Closed Workbook Based on Date

[Just Mike]

Hi Everyone

Hoping I can get some help on a Macro I am trying to create.

I have my from path and in the from path I have hundreds of files each with a difference date.

I would like to only copy data from the closed workbook that ends in COLLAT_COB_MMMMYYDD and where MMMMYYDD is equal to yesterdays business date.

So today is 1/22/2021 but the file would show COLLAT_COB 20210121

I have found macros that can copy over all the files but I am just looking to copy of the specific file based on the above criteria.

Thank you for your time

 

[Saurabhj]

Hi,

Compare your filename before doing any operation as below:

The dates in file name is in format YYYYMMDD while In the query you mentioned MMMMYYDD

Dim fileName As String
fileName = Mid(ThisWorkbook.Name, 12, 8)
if Left(fileName, 8) = CStr(Format(Now() - 1, "YYYYMMDD")) then
   'type your code
end if