How do I open a workbook in a directory based on file name and pull certain fields from it?

[TaxManiac]

Here's what I need to do:
I have a workbook (destination) that needs to open the latest version of a file (source) in a specified directory and pull certain fields from a lead sheet and place them in the destination workbook.
- the directory will only store versions the source file
- the source files will always be named with a date as the suffix (like "filename_010219"). I don't want to use last updated date since someone could touch the file and change that, if it's not current.
-Each time it is run, it has to pull the data from only the latest version of the source file located in the directory. All previous versions will be present.

I'm spinning my wheels here like you wouldn't believe and would appreciate anyone's help. Let me know if I need to include more details.

 

[ranman256]

place this code into your PERSONAL macros.
open the master file, then run: UpdLatestFile()

It will search that folder for the latest same name file grab the value in B7, then update the master B7.
(alter as needed)

Public Sub UpdLatestFile()
Dim wbSrc As Workbook, wbTarg As Workbook
Dim vDir, f, vVal


Set wbTarg = ActiveWorkbook
   'open the most recent file
getDirName wbTarg.FullName, vDir, f
vFile = getLatestDate(vDir)


If IsEmpty(vFile) Then
   MsgBox "No file found", vbCritical, "Missing file"
Else
    Workbooks.Open vFile, , True
    Set wbSrc = ActiveWorkbook
    vVal = Range("B7").Value
    ActiveWorkbook.Close False
    
    wbTarg.Activate
    Range("B7").Value = vVal
    wbTarg.Save
End If


Set wbSrc = Nothing
Set wbTarg = Nothing
End Sub


Private Function getLatestDate(ByVal pvDir)
  Dim oWinner
  Dim oFile
  Dim goFS
  Dim f
  
  f = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)      'get master filename, no extension


  Set goFS = CreateObject("Scripting.FileSystemObject")
  For Each oFile In goFS.GetFolder(pvDir).Files
      If InStr(oFile.Name, ".xls") > 0 And (oFile.Name) <> ActiveWorkbook.Name Then   'if this IS an excel file
         If InStr(oFile.Name, f) > 0 Then                                             'if this IS a variant of the master file
                If IsEmpty(oWinner) Then
                   Set oWinner = oFile
                Else
                   If oFile.DateLastModified > oWinner.DateLastModified Then Set oWinner = oFile
                End If
         End If
      End If
  Next
     
 getLatestDate = oWinner
End Function


Private Sub getDirName(ByVal psFilePath, ByRef prvDir, Optional ByRef prvFile)
    'psFilePath: full file path given
    'prvDir : directory name output
    'prvFile: filename only output
Dim i As Integer, sDir As String


i = InStrRev(psFilePath, "\")
If i > 0 Then
  prvDir = Left(psFilePath, i)
  prvFile = Mid(psFilePath, i + 1)
End If
End Sub