How to open a workbook with a variable date

[zombiemaster]

Hi, All...

I am trying to write code that will open a spreadsheet so it can be used for a series of vlookup formulas, but the file name has a date in it that changes as updates are made. I am pretty sure the solution is just to use a wildcard for the date piece of the file name, but I've never used wildcards before and haven't had any luck figuring it out.

When I use the wizard to record opening the file manually, here's the code that comes back:

Sub openthefile()
'
' openthefile Macro
'
    ChDir "\\ORHPDATNNH039\Data\di\client_services\Department Listings"
    Workbooks.Open Filename:= _
        "\\ORHPDATNNH039\Data\di\client_services\Department Listings\!Pallm Directory 11.14.18.xlsx" _
        , Notify:=False

End Sub

I need to turn that "11.14.18" date into a wildcard so it will open all the time regardless of which date it's changed to. Any help is appreciated!

Thanks,
~ZM~

 

[John_w]

See if this macro, Change_Link_Sources, works for you. It changes the file name of all external links whose source file name matches "*\!Pallm Directory ##.##.##.xlsx", searching for the first matching file name in the same directory as the current link. This is the same as manually changing the source in Edit Links on the Data tab. It also opens the external workbook.

The macro can be called from Workbook_Open in the ThisWorkbook module, so that the links are updated automatically when the workbook is opened:

Private Sub Workbook_Open()
    Change_Link_Sources
End Sub

And put the main macro in a standard module.

Public Sub Change_Link_Sources()

    Dim wb As Workbook
    Dim links As Variant
    Dim i As Long, p As Long
    Dim matchingFile As String
    
    'Update external link sources in this workbook and open the source workbooks
    
    Set wb = ThisWorkbook
    
    links = wb.LinkSources(xlExcelLinks)

    Application.ScreenUpdating = False

    For i = 1 To UBound(links)
        If links(i) Like "*\!Pallm Directory ##.##.##.xlsx" Then
            p = InStrRev(links(i), "\")
            matchingFile = Find_Matching_File(Left(links(i), p), "!Pallm Directory ##.##.##.xlsx")
            If matchingFile <> "" And matchingFile <> links(i) Then
                'The external file name is different to the current link so change the link to the found file
                wb.ChangeLink links(i), matchingFile, xlLinkTypeExcelLinks
            End If
            'Open the external workbook
            Workbooks.Open matchingFile, UpdateLinks:=False, Notify:=False
        End If
    Next
    
    wb.Activate
    
   Application.ScreenUpdating = True
    
    MsgBox "Activated " & wb.Name
        
End Sub


Private Function Find_Matching_File(path, fileNameLike As String) As String

    Dim fileName As String
    Dim matchingFile As String
    
    Find_Matching_File = ""
    
    If Right(path, 1) <> "\" Then path = path & "\"
    
    matchingFile = ""
    fileName = Dir(path & "*.*")
    While fileName <> vbNullString And matchingFile = ""
        If fileName Like fileNameLike Then matchingFile = fileName
        fileName = Dir
    Wend
    
    If matchingFile <> "" Then
        Find_Matching_File = path & matchingFile
    End If
    
End Function

The code uses the VBA Like operator, and its wildcard characters (e.g. * and #) are explained at https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/like-operator.

 

[zombiemaster]

Wow. Thanks, John - that is way above my skillset so I think I'll have to just do a couple of the steps manually for now until I can figure out everything you outlined above. Thanks!!

~ZM~

 

[John_w]

Yes, looking at the Edit Links dialogue will show you which external workbooks are referenced by formulas.

I should have said to test it (i.e. run the Change_Link_Sources macro) on a copy of your workbook, first. Also, if you change:

Set wb = ThisWorkbook

to:

Set wb = ActiveWorkbook

then you could have the macro in a separate .xlsm workbook and run it from a .xlsx file and it will update the links in that .xlsx file.

 

[zombiemaster]

Unfortunately there's much more that I DON'T know than I DO know about this stuff...most of the terminology you used is above me and I don't even know where to start trying to incorporate what you provided into my existing code. I got a runtime error pretty much immediately when i tried using it, and don't know where to go from there to fix it...lol

Oh well, it was worth a shot - thanks anyway!

~ZM~