Dynamic filename lookup in directory

[Gimics]

Hello,

I am using a macro to update an active sheet, with information from another worksheet. The second worksheet has a dynamic name, with a prefix that will always be consistent (let's call it "Source"), and 8 characters that will always equal the last 8 characters of my active worksheet. I need some help with a macro that will find the new worksheet for me, each month.

My active worksheet is named ("Destination XXYYZZZZ.xlsm") where XXYYZZZZ will be renamed as a date each month.

My source worksheet will be named ("Source XXYYZZZZ.xlsx") where XXYYZZZZ will always be the same date as the Destination worksheet.

"Source XXYYZZZZ.xlsx" will exist in a subfolder of the directory I would like to find it in, as I have separate folders for each year, and would like the macro to continue to be active when I switch years. As an example:

For 2013, the director was \Source\2013\Source XXYYYZZZZ.xlsx
For 2014, the director was \Source\2013\Source XXYYYZZZZ.xlsx

Once I have a macro that identifies the correct Source workbook, I will name it and then use it to copy and paste information from (which I know how to do).

Any thoughts?

 

[mrmmickle1]

Not sure I understand completely what you want but I think you should use something along these lines:

Sub OpenFile()


    [COLOR=#0000ff]Dim[/COLOR] DateString[COLOR=#0000ff] As String[/COLOR]
 [COLOR=#0000ff]   Dim [/COLOR]FileDirStr [COLOR=#0000ff]As String[/COLOR]
    
    FileDirStr = "\Source\2013\Source "
    DateString = Right(ActiveSheet.Name, 8)
    
    Application.Workbooks.Open (FileDirStr & DateString & ".xlsx")


[COLOR=#0000ff]End Sub[/COLOR]

 

[Gimics]

Thanks mrmmickle!

I think this is really close, but the issues is that the directory \Source\2013 could change year over year to \Source\2012 or \Source\2014\.

Instead, I would hope to look in \Source\ and it's sub directories for "Soure " & DateString (as per your variables).

 

[mrmmickle1]

Just use one more variable:

[COLOR=#0000ff]Sub [/COLOR]OpenFile()

[COLOR=#0000ff]    Dim[/COLOR] DateString [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] FileDirStr [COLOR=#0000ff]As String[/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] CurrYear   [COLOR=#0000ff]As String[/COLOR]
    
    CurrYear = Year(Date)
    FileDirStr = "\Source\" & CurrYear & "\Source"
    DateString = Right(ActiveSheet.Name, 8)
    
    Application.Workbooks.Open (FileDirStr & DateString)

[COLOR=#0000ff]End Sub[/COLOR]

 

[Gimics]

Thanks!! That was very simple, and works perfect.

 

[mrmmickle1]

Your welcome. Glad I could help.