Using VBA to Open a File Name that changes slightly

[souljive99]

Hi All,

I want to use vba to open a file where the name changes slightly each week it comes in. Let's say the file name is f:/Folder A 05212009

I can do this to open it:

Workbooks.Open Filename:=":/Folder A 05212009"

But, next week when the file name is Folder A 05282009, it won't open. Is there any way to write the code so that it picks up this pattern and can open it each week?

I greatly appreciate any help you can give me!

[JBeaucaire]

Try something like this...it will search ThePath you designate for the filenames in the format you named. It will start with Today's date and work backward until finds a file and opens it...then it will stop.

Option Explicit

Sub OpenByDate()
'Opens a sheet based on date search
Dim TheString As String, TheDate As Date, ThePath As String

ThePath = "C:\Documents and Settings\Jerry\Desktop\"
TheDate = Date
TheString = "Folder A " & WorksheetFunction.Text(TheDate, "MMDDYYYY") & ".xls"
    
    Do
        On Error Resume Next
        Workbooks.Open ThePath & TheString
    
        If Err <> 0 Then
            TheDate = TheDate - 1
            TheString = "Folder A " & WorksheetFunction.Text(TheDate, "MMDDYYYY") & ".xls"
        End If
    Loop Until Err = 0

End Sub

[souljive99]

Thank you very much, but this isn't working. Can I give you the exactly file names? The folder is C:\Documents and Settings\trevor\Desktop and the file name is "test 05.26.2009.xls"

I like the idea of starting with today's date and running back through, but it doesn't seem to be working....

[JBeaucaire]

That's because your original post demonstrated a filename format that does not match your need. Always best to start with ACTUAL need rather than something you'll have to interpret.

Here's the code amended to your current example. I've highlighted the portions in red where you could make adjustments:

Option Explicit

Sub OpenByDate()
'Opens a sheet based on date search
Dim TheString As String, TheDate As Date, ThePath As String

ThePath = "C:\Documents and Settings\trevor\Desktop\"
TheDate = Date
TheString = "test " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
    
    Do
        On Error Resume Next
        Workbooks.Open ThePath & TheString
    
        If Err <> 0 Then
            TheDate = TheDate - 1
            TheString = "test " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
        End If
    Loop Until Err = 0

End Sub

[souljive99]

It just sits with the hour glass and when I click Escape then debug, the part with "If Err <> 0 Then" is highlighted

[JBeaucaire]

Try running through the code in the VBEditor using the F8 key to issue one command at a time... When it highlights that line of code, put your mouse over the Err variable and see what the current value is.

I just used that code 3 times in a row to open varying files off of my computer. You could also triple check the path you gave me.

[souljive99]

Cool! This worked when I tried it again...Thank you so much...Actually have a follow up - can I use that same method to look into the most recent folder? The date format is YYYY_MM_DD

Basically, I'd like to open up a folder like the following:

C:\Holdings\2009_05_26

Thanks again for your help.

[JBeaucaire]

Open it when? For what purpose? As part of this macro?

You already see the formatting in the macro I've presented. You can play with ThePath variable the same way.

TheDate = Date
ThePath = "C:\Holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"

[souljive99]

Do you do separate loops for the path and the string, like this? or is this wrong...

Option Explicit

Sub OpenByDate()
'Opens a sheet based on date search
Dim TheString As String, TheDate As Date, ThePath As String

ThePath = "c:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"
TheDate = Date
TheString = "test " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
    
    Do
        On Error Resume Next
        Workbooks.Open ThePath
        
        If Err <> 0 Then
            TheDate = TheDate - 1
            ThePath = "c:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"
    
    Do
        On Error Resume Next
        Workbooks.Open TheString
    
        If Err <> 0 Then
            TheDate = TheDate - 1
            TheString = "test " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
        End If
    Loop Until Err = 0

End Sub

[shg]

souljive,

Please edit your post to add code tags.

[souljive99]

Sorry, I'm new...Is this how you tag codes?

Do you do separate loops for the path and the string, like this? or is this wrong...

Option Explicit

Sub OpenByDate()
'Opens a sheet based on date search
Dim TheString As String, TheDate As Date, ThePath As String

ThePath = "c:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"
TheDate = Date
TheString = "test " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"

Do
On Error Resume Next
Workbooks.Open ThePath

If Err <> 0 Then
TheDate = TheDate - 1
ThePath = "c:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"

Do
On Error Resume Next
Workbooks.Open TheString

If Err <> 0 Then
TheDate = TheDate - 1
TheString = "test " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
End If
Loop Until Err = 0

End Sub

[shg]

No, it's not. The forum rules explain it, and so does the How To ... link in the menu bar.

[souljive99]

Sorry...hope this is right.

Anyway, I am not sure if you have do this loop for the path and the string or once for both...if someone could take a look at this and let me know what I am doing wrong I would really appreciate it. Thanks.

Option Explicit

Sub OpenByDate()
'Opens a sheet based on date search
Dim TheString As String, TheDate As Date, ThePath As String

ThePath = "c:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"
TheDate = Date
TheString = "test " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"

Do
On Error Resume Next
Workbooks.Open ThePath

If Err <> 0 Then
TheDate = TheDate - 1
ThePath = "c:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"

Do
On Error Resume Next
Workbooks.Open TheString

If Err <> 0 Then
TheDate = TheDate - 1
TheString = "test " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
End If
Loop Until Err = 0

End Sub

[JBeaucaire]

SJ, stop adding new posts with the same code. The moderators want you to go back and add those code tags to the PREVIOUS posts. You need to fix all of them, even the code in post #1.

==========
Also, you took my code idea and moved it around. What I gave you in post #8 was in a different order than what you put in post #9. You can't use the variable TheDate before you've even given it a value...

And your question really confuses me. You're telling me that would have FOLDER names with dates on them and then files INSIDE the folder that don't go WITH the same date? Isn't that intrinsically problematic?

[souljive99]

I apologize...I'm new to this, I'm trying to post things the right way. I think I understand now. Sorry for the error - I really do appreciate all the help.

What I am trying to do is look for the most recent folder that has a date in it, say :

F:\holdings\2005_05_21

Then, in that folder I want to find the specified file, which is in this format:

F:\holdings\2005_05_21\portfolio 05.21.2009.xls

Of courses, those dates change every week. I want to open that file so I can change it around with a macro and save it again with a different name.

As I said before - I really appreciate all the help and sorry for the multiple posts.

[shg]

souljive, you broke the code, thank you.

[JBeaucaire]

Did you mean to demonstrate year in 2005 for the folder and a year of 2009 for the file? I'm going to presume that was an error and just make them the same:

Option Explicit

Sub OpenByDate()
'Opens a sheet based on date search
Dim TheString As String, TheDate As Date, ThePath As String

TheDate = Date
ThePath = "C:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"
TheString = "portfolio " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
On Error Resume Next

    Do
        Workbooks.Open ThePath & TheString
    
        If Err <> 0 Then
            TheDate = TheDate - 1
            ThePath = "C:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"
            TheString = "portfolio " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
        End If
    Loop Until Err = 0

End Sub

[souljive99]

I'm using the f:\ drive. Do I need to use a Change Directory function or should it work? I copied your script exactly and its not picking it up.

[JBeaucaire]

Find the two references to the C:\ drive in the macro and change them accordingly.

[souljive99]

In the vba script, when I put the curser over the words TheDate it says "TheDate = 4/28/1996" and when I put the curser over Date it says "Date = 2/2/09" I think this may be why it's not working. Do you know what could be wrong? Thanks.

[JBeaucaire]

In your VBEditor, press CTRL-G to bring up the Immediate window. Then enter these two lines of code one at a time into the window and see if the msgbox that pops up has the correct date or not:

TheDate = Date


MsgBox TheDate

If you want to work on this in realtime, my Skype ID is devtuxx7069. Just click the green checkmark across from name under the Location: info.

I'm sure there's something simple tweaked on your system somewhere, the code is pretty basic.

[souljive99]

Unfortunately I can't use skype on this computer.

This is the code I am using:

Option Explicit

Sub OpenByDate()
'Opens a sheet based on date search
Dim TheString As String, TheDate As Date, ThePath As String

TheDate = Date
ThePath = "C:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"
TheString = "portfolio " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
On Error Resume Next

    Do
        Workbooks.Open ThePath & TheString
    
        If Err <> 0 Then
            TheDate = TheDate - 1
            ThePath = "C:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY_MM_DD") & "\"
            TheString = "portfolio " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
        End If
    Loop Until Err = 0

End Sub

These are the full file names:

folder - "C:\holdings\2009_05_10"

file - "portfolio 05.10.2009.xls"

When I did the immediate msgbox to bring up today's date, it worked (it brought up 5/26/2009)

Whenever I run the macro the hourglass just continues to run and when I hit escape and debug, the If Err <> 0 part is highlighted...any ideas?

[JBeaucaire]

Sorry SJ. The code looks fine. It opens a file for me when I create the folder and file on my system.

The only unknown is your computer.

[souljive99]

I think I figured out the problem but I don't know the solution...

When I put the date format as YYYY_MM_DD it picks up the date as 2009 5 26

Any idea on how to fix this so it picks it up as 2009_05_26 (with the underscores and the 0 in front of the 5)

[JBeaucaire]

OK, something about that pesky underscore doesn't want to evaluate inside the TEXT function, so I cheated. I used another character (¢) and then used a substitute function to change it INTO an underscore. Problem solved.

Sub OpenByDate()
'Opens a sheet based on date search
Dim TheString As String, TheDate As Date, ThePath As String

TheDate = Date
ThePath = "C:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY¢MM¢DD") & "\"
ThePath = WorksheetFunction.Substitute(ThePath, "¢", "_")
TheString = "portfolio " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
On Error Resume Next

    Do
        Workbooks.Open ThePath & TheString
    
        If Err <> 0 Then
            TheDate = TheDate - 1
            ThePath = "C:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY¢MM¢DD") & "\"
            ThePath = WorksheetFunction.Substitute(ThePath, "¢", "_")
            TheString = "portfolio " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
        End If
    Loop Until Err = 0
    
End Sub

[souljive99]

Thanks! That works well...only problem is that the hour glass remains on the screen after the file is opened...so I can't continue the script to alter the opened excel sheet. Shouldn't the loop until...part do that?

[JBeaucaire]

Interesting. It stops on my system. We can add a "stop" command I suppose. Once more...:

Sub OpenByDate()
'Opens a sheet based on date search
Dim TheString As String, TheDate As Date, ThePath As String
Dim TheBook As Workbook

TheDate = Date
ThePath = "C:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY¢MM¢DD") & "\"
ThePath = WorksheetFunction.Substitute(ThePath, "¢", "_")
TheString = "portfolio " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
On Error Resume Next

    Do
        Workbooks.Open ThePath & TheString
        Set TheBook = Workbooks(TheString)
    
        If TheBook Is Nothing Then 'Not open
            TheDate = TheDate - 1
            ThePath = "C:\holdings\" & WorksheetFunction.Text(TheDate, "YYYY¢MM¢DD") & "\"
            ThePath = WorksheetFunction.Substitute(ThePath, "¢", "_")
            TheString = "portfolio " & WorksheetFunction.Text(TheDate, "MM.DD.YYYY") & ".xls"
        Else                     'It opened
            Set TheBook = Nothing
            Exit Sub
        End If
    Loop

MsgBox "You should never see this message"
End Sub

[slaneues]

Hello,

This is a really useful conversation. I have learned a lot from reading it. I am also relatively new and have a related question I thoink could be answered realtively easily (or so I hope!).

I am trying to not only open a file that has a date component that is important, but also has a time stamp that I want to ignore.

For example, the file from today could either be 20120904_0900.xls (if it was run at 9AM) or 20120904_0843 (if it was run at 8:43 AM). I would like to open it whatever time it gets produced. I know it has something to do with the * function (like *.xls to open or copy every excel sheet in a folder) I have seen so often on this site but I cannot quite figure out exactly how to do it.

Any help would be greatly appreciated.

Thanks!

Shannon

[Cutter]

Hello slaneues, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[slaneues]

Sorry! I am relatively new and forgot some of the rules. I found a solution to this, but thanks anyway!

Shannon

[scott.s.fower]

Is there any way that we can set up the code skip the message box that says "Path\FileName.xls cannot be found. Check your spelling or try a different path." so it will auto loop until it opens, otherwise if it is a monthly document you have to hit ok 30 times until it opens

[arlu1201]

Scott,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

[halaami8]

Hello,

I am also trying to do the same thing. But instead of an .xls file it a PowerPoint one. I am using a macro to add a PowerPoint file as an object.
The file's name is also a date. What is the equivalent of WorksheetFunction.Text(TheDate, "YYYY_MM_DD") function in VBA for PowerPoint?

I would appreciate your help.

[Fotis1991]

halaami8

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.