VBA from latest file in a folder ("Run-time error 1004")-CodePudding

I used the below code to open the latest file in a folder and Vlookup from it and return the value which is in column I.

I am facing a Run-time error 1004, although everything is correct.

i Set wbname = ActiveWorkbook.Name to catch the open sheet name which I will put the Vlookup formula in and I am choosing the correct range for my formula which is I2, still can't figure out where did I go wrong. Error Message in the below line:

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

My Code:

 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)"

CodePudding user response：

Like I mentioned VLOOKUP works on closed file as well. There is no need to open the file.

Your [ and ] and ' placement is incorrect. Here is an example (Untested)

If you manualy type the formula, it will look like this

=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

EDIT

This is how your original code can be written. I have commented the code so you should not have any problem understanding it.

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

Screenshot