VBA

Alvaroro84

Board Regular
Joined
May 13, 2022
Messages
65
Office Version
  1. 2016
Platform
  1. Windows
I want the macro to pick up the most recent Xl Doc from the My path=F:\VMWare\" location I'm just not sure what else to add to make it pick the most recent

VBA Code:
Sub macro2()
'
'
Dim LastRow As Long
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim WB As Workbook
Set WB = ActiveWorkbook
LastRow = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row


MyPath ="F:\VMWare\"



Range("D2").Select
ActiveCell.Formula2R1C1 = _
      "=XLOOKUP(RC[-3],'[" & MyPath & "]Cos'!C3,'[" & MyPath & "]Cos'!C4,0)"


Selection.AutoFill Destination:=Sheets("Sheet1").Range("D2:D" & LastRow), Type:=xlFillDefault


End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi.

Add the following function to your VBA code module:
VBA Code:
Function Most_Recently_Modified_ExcelFile_In_This_Folder(folderPath As String, fileExtension As String)
fileExtension = Replace(fileExtension, ".", "")
If Right(folderPath, 1) = "\" Then folderPath = Left(folderPath, Len(folderPath))

Dim xFolder, xFile, fileName As String, counter As Integer, latestDate As Date
counter = 0
With CreateObject("Scripting.FileSystemObject")
    Set xFolder = .GetFolder(folderPath)
    For Each xFile In xFolder.Files
        If Mid(xFile.Name, InStrRev(xFile.Name, ".") + 1, 3) = fileExtension Then
            If counter = 0 Then
                fileName = xFile.Name
                latestDate = xFile.DateLastModified
                counter = 1
            Else
                If xFile.DateLastModified > latestDate Then
                    latestDate = xFile.DateLastModified
                    fileName = xFile.Name
                End If
            End If
        End If
    Next xFile
End With
Most_Recently_Modified_ExcelFile_In_This_Folder = fileName
End Function

And to use it in your code, after:
VBA Code:
MyPath = "F:\VMWare\"
put
VBA Code:
LatestFile = Most_Recently_Modified_ExcelFile_In_This_Folder(MyPath, "xls")

And change:
VBA Code:
      "=XLOOKUP(RC[-3],'[" & MyPath & "]Cos'!C3,'[" & MyPath & "]Cos'!C4,0)"
to (this will detect .xls, .xlsx, .xlsb, .xlsm, etc.)
VBA Code:
      "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"


Please let us know if that works for you.
 
Last edited:
Upvote 0
Hi.

Add the following function to your VBA code module:
VBA Code:
Function Most_Recently_Modified_ExcelFile_In_This_Folder(folderPath As String, fileExtension As String)
fileExtension = Replace(fileExtension, ".", "")
If Right(folderPath, 1) = "\" Then folderPath = Left(folderPath, Len(folderPath))

Dim xFolder, xFile, fileName As String, counter As Integer, latestDate As Date
counter = 0
With CreateObject("Scripting.FileSystemObject")
    Set xFolder = .GetFolder(folderPath)
    For Each xFile In xFolder.Files
        If Mid(xFile.Name, InStrRev(xFile.Name, ".") + 1, 3) = fileExtension Then
            If counter = 0 Then
                fileName = xFile.Name
                latestDate = xFile.DateLastModified
                counter = 1
            Else
                If xFile.DateLastModified > latestDate Then
                    latestDate = xFile.DateLastModified
                    fileName = xFile.Name
                End If
            End If
        End If
    Next xFile
End With
Most_Recently_Modified_ExcelFile_In_This_Folder = fileName
End Function

And to use it in your code, after:
VBA Code:
MyPath = "F:\VMWare\"
put
VBA Code:
LatestFile = Most_Recently_Modified_ExcelFile_In_This_Folder(MyPath, "xls")

And change:
VBA Code:
      "=XLOOKUP(RC[-3],'[" & MyPath & "]Cos'!C3,'[" & MyPath & "]Cos'!C4,0)"
to (this will detect .xls, .xlsx, .xlsb, .xlsm, etc.)
VBA Code:
      "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"


Please let us know if that works for you.
How do I add the function?. Also I did have a small issue with this section here
VBA Code:
Selection.AutoFill Destination:=Sheets("Sheet1").Range("D2:D" & LastRow), Type:=xlFillDefault
This part of the macro when using a filter it auto fills more then the visible data. Is there a way to use this format stile auto fill to only capture visible cells?
 
Last edited:
Upvote 0
How do I add the function?. Also I did have a small issue with this section here
VBA Code:
Selection.AutoFill Destination:=Sheets("Sheet1").Range("D2:D" & LastRow), Type:=xlFillDefault
This part of the macro when using a filter it auto fills more then the visible data. Is there a way to use this format stile auto fill to only capture visible cells?

To make things simpler, delete all of the code you presented in your first post from the Excel Workbook. Then copy all of the the code below and paste it where that code was. And see if it works for you.
VBA Code:
Sub macro2()

Dim LastRow As Long
LastRow = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row

Dim MyPath As String
MyPath = "F:\VMWare\"

Dim LatestFile As String
LatestFile = Most_Recently_Modified_ExcelFile_In_This_Folder(MyPath, "xls")

ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"

Sheets("Sheet1").Range("D2:D" & LastRow).Cells.SpecialCells(xlCellTypeVisible).Formula2 = ActiveCell.Formula2

End Sub

Function Most_Recently_Modified_ExcelFile_In_This_Folder(folderPath As String, fileExtension As String)
fileExtension = Replace(fileExtension, ".", "")
If Right(folderPath, 1) = "\" Then folderPath = Left(folderPath, Len(folderPath))
Dim xFolder, xFile, fileName As String, counter As Integer, latestDate As Date
counter = 0
With CreateObject("Scripting.FileSystemObject")
    Set xFolder = .GetFolder(folderPath)
    For Each xFile In xFolder.Files
        If Mid(xFile.Name, InStrRev(xFile.Name, ".") + 1, 3) = fileExtension Then
            If counter = 0 Then
                fileName = xFile.Name
                latestDate = xFile.DateLastModified
                counter = 1
            Else
                If xFile.DateLastModified > latestDate Then
                    latestDate = xFile.DateLastModified
                    fileName = xFile.Name
                End If
            End If
        End If
    Next xFile
End With
Most_Recently_Modified_ExcelFile_In_This_Folder = fileName
End Function
 
Last edited:
Upvote 0
Solution
To make things simpler, delete all of the code you presented in your first post from the Excel Workbook. Then copy all of the the code below and paste it where that code was. And see if it works for you.
VBA Code:
Sub macro2()

Dim LastRow As Long
LastRow = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row

Dim MyPath As String
MyPath = "F:\VMWare\"

Dim LatestFile As String
LatestFile = Most_Recently_Modified_ExcelFile_In_This_Folder(MyPath, "xls")

ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"

Sheets("Sheet1").Range("D2:D" & LastRow).Cells.SpecialCells(xlCellTypeVisible).Formula2 = ActiveCell.Formula2

End Sub

Function Most_Recently_Modified_ExcelFile_In_This_Folder(folderPath As String, fileExtension As String)
fileExtension = Replace(fileExtension, ".", "")
If Right(folderPath, 1) = "\" Then folderPath = Left(folderPath, Len(folderPath))
Dim xFolder, xFile, fileName As String, counter As Integer, latestDate As Date
counter = 0
With CreateObject("Scripting.FileSystemObject")
    Set xFolder = .GetFolder(folderPath)
    For Each xFile In xFolder.Files
        If Mid(xFile.Name, InStrRev(xFile.Name, ".") + 1, 3) = fileExtension Then
            If counter = 0 Then
                fileName = xFile.Name
                latestDate = xFile.DateLastModified
                counter = 1
            Else
                If xFile.DateLastModified > latestDate Then
                    latestDate = xFile.DateLastModified
                    fileName = xFile.Name
                End If
            End If
        End If
    Next xFile
End With
Most_Recently_Modified_ExcelFile_In_This_Folder = fileName
End Function
I got a run time error '1004'
application defined or object-defined error and it highlights the code below
VBA Code:
ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"
 
Upvote 0
I got a run time error '1004'
application defined or object-defined error and it highlights the code above

I got a run time error '1004'
application defined or object-defined error and it highlights the code below
VBA Code:
ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"
 
Upvote 0
To make things simpler, delete all of the code you presented in your first post from the Excel Workbook. Then copy all of the the code below and paste it where that code was. And see if it works for you.
VBA Code:
Sub macro2()

Dim LastRow As Long
LastRow = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row

Dim MyPath As String
MyPath = "F:\VMWare\"

Dim LatestFile As String
LatestFile = Most_Recently_Modified_ExcelFile_In_This_Folder(MyPath, "xls")

ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-3],'[" & MyPath & LatestFile & "]Cos'!C3,'[" & MyPath & LatestFile & "]Cos'!C4,0)"

Sheets("Sheet1").Range("D2:D" & LastRow).Cells.SpecialCells(xlCellTypeVisible).Formula2 = ActiveCell.Formula2

End Sub

Function Most_Recently_Modified_ExcelFile_In_This_Folder(folderPath As String, fileExtension As String)
fileExtension = Replace(fileExtension, ".", "")
If Right(folderPath, 1) = "\" Then folderPath = Left(folderPath, Len(folderPath))
Dim xFolder, xFile, fileName As String, counter As Integer, latestDate As Date
counter = 0
With CreateObject("Scripting.FileSystemObject")
    Set xFolder = .GetFolder(folderPath)
    For Each xFile In xFolder.Files
        If Mid(xFile.Name, InStrRev(xFile.Name, ".") + 1, 3) = fileExtension Then
            If counter = 0 Then
                fileName = xFile.Name
                latestDate = xFile.DateLastModified
                counter = 1
            Else
                If xFile.DateLastModified > latestDate Then
                    latestDate = xFile.DateLastModified
                    fileName = xFile.Name
                End If
            End If
        End If
    Next xFile
End With
Most_Recently_Modified_ExcelFile_In_This_Folder = fileName
End Function
Is the [" & MyPath & LatestFile & "] incorrect perhaps
 
Upvote 0
Yeah, I saw that. But I can't guess what's wrong.

Is the [" & MyPath & LatestFile & "] incorrect perhaps
That could be it. Just add the following line above the line that's giving the error, run the sub again (from the code window, if you are not doing that already), and press Ctrl G. You will see a bottom window under the divide display the file path. And see if it's correct. (You have to X out of that smaller window to close it/maximize the normal code window again.)
VBA Code:
Debug.Print MyPath & LatestFile
 
Upvote 0
Yeah, I saw that. But I can't guess what's wrong.


That could be it. Just add the following line above the line that's giving the error, run the sub again (from the code window, if you are not doing that already), and press Ctrl G. You will see a bottom window under the divide display the file path. And see if it's correct. (You have to X out of that smaller window to close it/maximize the normal code window again.)
VBA Code:
Debug.Print MyPath & LatestFile
Still getting the same error code but i can confirm that it is picking up the correct file path
 
Upvote 0
In the formula that you originally had (and is at the beginning of the line that is giving the error), there is:
VBA Code:
RC[-3]
So that may indicate that the contents in 3 columns to the left of where the formula is being applied (in the active sheet in your Workbook with the VBA) may be wrong.
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top