VLOOKUP Within Changing File - Data Consolidation Macro

[Renee Beacham]

Hello all,

I am attempting to design a macro file which would pull data from other files via vlookup into a scorecard-like excel table.

This is simple enough in theory, however the files which the vlookups would pull from change since they are updated daily.

For example I want to pull days worked for a specific employee from file Days_Worked_3_22_2016.xlsx. This file is refreshed daily and saved in the same folder location with the new date. The next day's file would be Days_Worked_3_23_2016.xlsx.

Is there a way I can write the VBA in order to have the macro pull the data into the scorecard table from changing file names per this pattern?

Thanks,
-Renee

 

[Renee Beacham]

Does anyone know how to code the VBA to do the vlookup within the file with the most recent date in the folder?

 

[Debaser]

Do you only have links to the one external workbook from your scorecard file?

 

[Renee Beacham]

I will need to have links to various different workbooks. There will be multiple different vlookups to pull different metrics into the scorecard. The vlookups pulling from each external workbook will reference the same cell every time the scorecard is updated. The only difference between scorecard updates is the file the macro needs to open for each vlookup. This is where I don't know what to do.

If it wasn't for the updating file names, I would simply create an excel with vlookups to other excel files and then refresh the data. But because the data refreshes are saved with a new file name for each data field in the scorecard, I need to create a macro file to pull the data from a changing source.

I've attached a screenshot of what a simple version of the scorecard will look like.

Thanks

 

[Debaser]

If the file name formatting is consistent, all you really need is a code that replaces 3_22_2016 with 3_23_2016 in your worksheet formulas.

 

[Renee Beacham]

That makes sense. I'm newer to coding and so don't know how to write the VBA code to change the formulas sequentially each time the macro runs. Could this be a REPLACE function?

 

[Debaser]

Basically yes. If it's always replacing yesterday's date with today's, and in that format, you could use:

Activesheet.Cells.Replace What:=Format(Date - 1, "m_d_yyyy"), Replacement:=Format(Date, "m_d_yyyy"), LookAt:=xlPart

If you have a lot of formulas, it would be more efficient to change the external link names instead.

 

[Debaser]

To change the links would be something like this:

Sub ChangeLinks()
    Dim arrLinks
    Dim i                As Long
    arrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(arrLinks) Then
        On Error Resume Next
        Application.DisplayAlerts = False
        For i = LBound(arrLinks) To UBound(arrLinks)
            ActiveWorkbook.ChangeLink arrLinks(i), Replace$(arrLinks(i), Format(Date - 1, "m_d_yyyy"), Format(Date, "m_d_yyyy")), xlLinkTypeExcelLinks
        Next i
        Application.DisplayAlerts = True
    End If
End Sub

 

[Renee Beacham]

Okay great thanks! I will try incorporating this into my Macro.

This was very helpful.

-Renee