Open excel file from a folder which changes name daily

[AndyM90]

Hi All,

There is a group of excel files which are automatically saved in a folder daily. I would like to be able to automatically open the documents and copy the data out of these into a central file. Normally this is straight forward, but the problem I have is that the folder changes name daily to be workday-1. Is there a way to have the macro search for the folder name = to workday-1 then open the files within it?

Details:

Folder location: "C:\Users\AM\Desktop\Rec\"
Folder name format: YYYYMMDD i.e. todays download is in a folder called 20140901, tomorrow it will be 20140902 etc etc
File name: ABC.xls

So in summary, I want a macro to open a document which has the same name every day, but the folder it is stored in changes. Any ideas if this is possible?

Thanks!

 

[VoG]

Try

Workbooks.Open Filename:="C:\Users\AM\Desktop\Rec\" & Format(Date - 1, "YYYYMMDD") & "\ABC.XLS"

 

[AndyM90]

thats works perfectly, thanks!

 

[AndyM90]

oh actually, will Date-1 work over weekends? So on a Monday the data I need will be a folder named with Friday's date? I've tried to adjust the code to the below, but it searches for a folder date of 18991229.

" & Format(WorkDay - 1, "YYYYMMDD") & "\ABC.xls

Any ideas?

 

[steve the fish]

How about:

If Format(Date - 1, "DDDD") = "Sunday" Then
    myDate = Format(Date - 3, "YYYYMMDD")
Else
    myDate = Format(Date - 1, "YYYYMMDD")
End If
Workbooks.Open Filename:="C:\Users\AM\Desktop\Rec\" & myDate & "\ABC.XLS"

 

[AndyM90]

Thanks Steve, that seems to work. But I'll have to check properly at the weekend. Thanks for the help