How to open a workbook with a variable date

zombiemaster

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

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,999
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:

zombiemaster

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

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,999
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.
 

zombiemaster

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

Forum statistics

Threads
1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top