Table reference to another workbook with constantly changing name and date


Hi, everybody!

I have a formula that contains reference to a table in another workbook.

The problem is that the other workbook is replaced every week with a new version. The table's format remains the same but the data and the file name changes in every new version.

The file name changes in the following manner - "New Table 09.03.2017" to "New Table 16.03.2017" 

So I need to either write a macros that:

1) refers to a table in the most recent workbook (with the latest date modified) 

2) refers to a table with the newest date in the file name

How can I write a macros on this?




This is not the simplest thing to do and it requires a couple macros. The second one sorts the dates and I just copy/pasted it in there.

Sub GetFileName()

Dim file, dateStart, myDate As Variant
Dim dateArray() As Date

file = Dir("D:\test\")

i = 1

While (file <> "")

    dateStart = InStrRev(file, " ") + 1

    myDate = Replace(Mid(file, dateStart, 10), ".", "/")

    ReDim Preserve dateArray(0 To i) As Date

    dateArray(UBound(dateArray)) = CDate(myDate)

    i = i + 1

    file = Dir


SortAr dateArray

MsgBox Replace(dateArray(UBound(dateArray)), "/", ".")

End Sub
Sub SortAr(arr() As Date)
    Dim Temp As Date
    Dim i As Long, j As Long

    For j = 2 To UBound(arr)
        Temp = arr(j)
        For i = j - 1 To 1 Step -1
            If (arr(i) <= Temp) Then GoTo 10
                arr(i + 1) = arr(i)
        Next i
        i = 0
10:     arr(i + 1) = Temp
    Next j
End Sub

In the first macro change D:\test\ to the location of the directory that contains the files.

This macro is setup to give you the most recent date from the files that have the structure that you mentioned and it seems to be working just fine.

Currently, the macro outputs the most recent date in a message box so you can see the result and make sure it works; once it looks fine to you, just replace MsgBox (in the last line of the first macro) with something like mostRecentDate = 

Once you are able to get the correct date value, just use that to create the reference to the new file.


Answer the Question

You must create an account to use the forum. Create an Account or Login