來自檔案夾中最新檔案的VBA（“運行時錯誤1004”）

我使用下面的代碼打開檔案夾中的最新檔案并從中進行 Vlookup 并回傳 I 列中的值。

我面臨一個運行時錯誤 1004，盡管一切都是正確的。

我設定 wbname = ActiveWorkbook.Name 來捕捉打開的作業表名稱，我將把 Vlookup 公式放入其中，我正在為我的公式選擇正確的范圍 I2，但仍然無法弄清楚我哪里出錯了。以下行中的錯誤訊息：

Range("I2").Formula = _
      "=VLOOKUP(A2,[" & MyPath & LatestFile & "]'Sheetname with input data'!A:I,9,False)"

我的代碼：

 Sub PrepareforOutlookMails()
    wbname = ActiveWorkbook.Name
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Dim wb As Workbook
    Dim fileLocation As String
    Dim fileToOpen As Workbook
    
    
    MyPath = "C:\1.ER\1.Work\19.Etr\Recon\2022\October"
        If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
        
        'first Excel file from the folder
        MyFile = Dir(MyPath & "*.xls", vbNormal)
        
        'If no files exit the sub
        If Len(MyFile) = 0 Then
            MsgBox "No files were found...", vbExclamation
            Exit Sub
        End If
        
        'Loop through each Excel file in the folder
        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
    Workbooks(wbname).Activate
    
    Range("I2").Formula = _
      "=VLOOKUP(A2,[" & MyPath & LatestFile & "]'Sheetname with input data'!A:I,9,False)"

uj5u.com熱心網友回復：

就像我提到VLOOKUP的關于封閉檔案的作品。無需打開檔案。

您的[and]和'放置不正確。這是一個例子（未經測驗）

如果您手動鍵入公式，它將如下所示

=VLOOKUP(D2,'C:\1.ER\1.Work\19.Etr\Recon\2022\October\[Mail Merge (Updated Sample File) (1).xlsx]Sheetname with input data'!A:I,9,0)

Sub Sample()
    Dim MyPath As String
    Dim LatestFile As String
    
    MyPath = "C:\1.ER\1.Work\19.Etr\Recon\2022\October\"
    LatestFile = "Mail Merge (Updated Sample File) (1).xlsx"
    
    Range("I2").Formula = "=VLOOKUP(A2,'" & _
                          MyPath & _
                          "[" & _
                          LatestFile & _
                          "]Sheetname with input data'!A:I,9,0)"
End Sub

編輯

這就是您的原始代碼的撰寫方式。我已經對代碼進行了注釋，因此您理解它應該沒有任何問題。

Option Explicit

Sub PrepareforOutlookMails()
    Dim wbThis As Workbook
    Dim wsThis As Worksheet
    
    Dim MyPath As String
    Dim MyFile As String
    Dim LMD As Date
    Dim LatestFile As String
    Dim LatestDate As Date
    
    Set wbThis = ThisWorkbook
    
    '~~> Change this to the relevant sheet
    '~~> This is where the formula will be written
    Set wsThis = wbThis.Sheets("Sheet1")

    'MyPath = "C:\1.ER\1.Work\19.Etr\Recon\2022\October"
    MyPath = "C:\Users\routs\Desktop"
    
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
        
    '~~> First Excel file from the folder
    MyFile = Dir(MyPath & "*.xls*", vbNormal)
        
    '~~> If no files exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
        
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop
    
    wsThis.Range("I2").Formula = "=VLOOKUP('" & wsThis.Name & "'!A2,'" & _
                                MyPath & _
                                "[" & _
                                LatestFile & _
                                "]Sheetname with input data'!A:I,9,0)"
End Sub

截屏

標籤：擅长vba

上一篇：VBA-非常慢地回圈遍歷行以復制/粘貼值

下一篇：如果一個值相同，VBAExcel僅在一封電子郵件中發送所有資訊