Find/Replace? to change a sheet name in a formula

[MPBJR]

I have a range on a sheet (Q89:S100) that I have formulas in to pull data from a file on a server. Every day this file gets updated and a new sheet is added with that days data on it, the sheet has a name format of 031819. I need to have a macro that runs at 5pm everyday that changes the sheet name in the formula to the newest sheet in the source file to get the most updated data.

For example, the formula I have in Q89 is:
='\\server\folder1\folder2\[source file.xlsx]031319'!B2

So the next day, i need to update all the formulas to pull data from 031419 (B2 will remain the same).

I've tried various macros for find & replace but can't get them to work.

I've set up 2 cells as a find value and replace value but can't get it to work.

I know how to set up the macro to run at a certain time, just can't figure out this find and replace part.

Any help would be greatly appreciated. Thanks

Sub UPDATE()
'
' UPDATE Macro
'

    
    
    
    Dim fnd As String
    Dim rplc As String

    fnd = Cells(100, "J").Value
    rplc = Cells(100, "K").Value
    Range("Q89:S100").Select
    Selection.Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[Fluff]

The only way I know is to have the file open. Are you happy for it to be opened?

 

[MPBJR]

I guess we could open the file, run the check, and then close the file ??

 

[Fluff]

Thinking about it, try

Sub UPDATE()
'
' UPDATE Macro
    Dim fnd As String
    Dim rplc As String
    Dim x As Variant

    fnd = Format(Cells(100, "J").Value, "mmddyy")
    rplc = Format(Cells(100, "K").Value, "mmddyy")
    x = Evaluate("isref('\\server\folder1\folder2\[source file.xlsx]" & rplc & "'!B2)")
    If x = True Then
         Range("Q89:S100").Replace what:=fnd, Replacement:=rplc, LookAt:=xlPart, _
             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
             ReplaceFormat:=False
    End If
End Sub

Not sure if will work with UNC rather than drive letter.