How to open a workbook with a variable date

zombiemaster

Board Regular
Joined
Oct 27, 2009
Messages
241
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:

Code:
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~
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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:

Code:
Private Sub Workbook_Open()
    Change_Link_Sources
End Sub

And put the main macro in a standard module.

Code:
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.
 
Last edited:
Upvote 0
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~
:cool:
 
Upvote 0
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:

Code:
Set wb = ThisWorkbook
to:
Code:
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.
 
Upvote 0
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~
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top