如何在代码中使用最新的文件函数执行vlookup

Question

你好，我使用一个函数来调用我文件夹中的最新文件。我需要为这个文件自动化一个vlookup。我在正确调用文件时遇到了问题

我已经试着按它的路径叫它了，但我相信我没有正确地称呼它。

Sub oversub()

    'Newest file function

        Dim MyPath As String
        Dim MyFile As String
        Dim LatestFile As String
        Dim LatestDate As Date
        Dim LMD As Date

        MyPath = "C:\Users\TAmon1\Desktop\OverSubscription Dash"
        If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
        MyFile = Dir(MyPath & "*.csv", vbNormal)
        If Len(MyFile) = 0 Then
            MsgBox "No files were found...", vbExclamation
            Exit Sub
        End If
        Do While Len(MyFile) > 0
            LMD = FileDateTime(MyPath & MyFile)
            If LMD > LatestDate Then
                LatestFile = MyFile
                LatestDate = LMD
            End If
            MyFile = Dir
        Loop
        Workbooks.Open MyPath & LatestFile
    Dim wb As Workbook
    'wbstring = MyPath & LatestFile
    Windows("Planning_tool.xlsm").Activate


'Vlookupfunction
    Range("N2").Select
    ActiveCell.FormulaR1C1=VLOOKUP(C[-13],'Router_level_crosstab.csv'!C1:C11,11,FALSE)"

目前，我调用的直接文件，这是可行的，但当我有一个新的文件，我将需要手动更改文件名。我需要将最新的文件合并到Vlookup中。

Answer 1

Dim wbCSV As Workbook, wbPlanning as Workbook
Set wbPlanning = Workbooks("Planning_tool.xlsm")
'...
'...
Set wbCSV = Workbooks.Open(MyPath & LatestFile) '<< save the reference to the opened file
'...
'...
'don't rely on activeworkbook, activesheet, etc
wbPlanning.Sheets(1).Range("N2").FormulaR1C1 = _
         "=VLOOKUP(C[-13],'" & wbCSV.Name & "'!C1:C11,11,FALSE)"