Macro: check today date with start date and open a file in stored location

Hi all,

I would like to kindly ask you if someone of you could help me to create a marco for the next.

I have a classic table (see attached addition) where is written start date/duration/forecast/completion/status.
I would like that the marco after open my excel file will always check the today date and compare it with start date. In case that this two dates will match it will automaticaly open a file in the location of my computer: C:\Users\tdubois\Documents . This macro should be able to open more files which are stored in the same location in case that today date will match with more start dates.

Thank you for all your help.

Regards

you are missing the file names in table

Kind regards
Leo

I have updated the table with name of the file. Now the location is as follow.
C:\Users\tdubois\Documents\Test

Thank you

Regards

something like this

Please Login or Register  to view this content.

Kind regards
Leo

Hi Leo,

I think it will works, but what should I modify in case that I want to change the storage location of my files from C:\Users\tdubois\Documents\Test for example to C:\Users\tdubois\Documents\SAP.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Sheets("Time")
lastrow = .Range("B" & Rows.Count).End(xlUp).Row
For x = 80 To lastrow Step 2
If .Range("B" & x).Value = Date Then
mylen = Len(.Range("Q" & x))
mystart = Application.Search("C:\", .Range("Q" & x))
myfile = Mid(.Range("Q" & x), mystart, mylen - (mystart - 1))
Workbooks.Open (myfile)
End If
Next
Application.Goto .Range("B" & lastrow), scroll:=False
End With
Application.ScreenUpdating = True
End Sub

Thank you for your great help.

Regards,

Hi Topa2,

like it is now the code search for the file in merged columns "Q:Y"
that was also the reasen for asking the file name in my first reaction.
so without seperate column for the filename simpel change test to SAP in merged columns.

Kind regards
Leo

No, I have to see the file

Kind regards
Leo

Hi Leo,

I tested your marco yesterday evening and it worked well. Thank you once again for your help!

I would like to ask you one more thing. Is it possible to create a marco with the same way as you have done it means that it will check today date compare it with the starting date and if these dates are matching it will open a file from the storage location? The thing is that the files doesn´t have the same method of naming as those are different working formular.

Thank you

Regards,

It is possible but the code has to know the name of the path and the name of the file.

Kind regards
Leo

So, please could you please help me with this?
You can use the same excel file as yesterday with the same storage location just the file name will be FQU770B. But I do not want to specify the path in my table. I would like to keep it just in the macro. As I´m using something around the 20 different file can you just explain to me if I can include all the name after in your code or how should I proceed in order to allow to your macro to search the different files following the starting date.

Thank you

Regards,

change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Kind regards
Leo

Hi Leo,

Sorry to disturb you again.
I tried to modify your code but without success. I have attached the same file juste with different name of two files which I need to automatically open. Some of the file can have the same starting date and some another can have a starting date different.
I would like to understand how to define in your code specification of each file linked to different starting date as after that I will be able to adjust it (maybe) allone .

Thank you once again

Regards,

Please Login or Register  to view this content.

Kind regards
Leo