Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Table reference to another workbook with constantly changing name and date

0

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?

Answer
Discuss

Answers

0

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

Wend

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.

Discuss


Answer the Question

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