VBA

A

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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
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
cmowla said:
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.
Click to expand...
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
Alvaroro84 said:
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?
Click to expand...

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
cmowla said:
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
Click to expand...
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
Alvaroro84 said:
I got a run time error '1004'
application defined or object-defined error and it highlights the code above
Click to expand...

Alvaroro84 said:
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)"
Click to expand...
 
Upvote 0
cmowla said:
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
Click to expand...
Is the [" & MyPath & LatestFile & "] incorrect perhaps
 
Upvote 0
Yeah, I saw that. But I can't guess what's wrong.

Alvaroro84 said:
Is the [" & MyPath & LatestFile & "] incorrect perhaps
Click to expand...
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
cmowla said:
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
Click to expand...
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
You must log in or register to reply here.

Similar threads

D
  • Solved
Copying file path from a cell?
Replies
2
Views
393
Dave_george
D
A
  • Solved
Macro help
Replies
8
Views
593
Alex Blakenburg
A
A
  • Question Question
Macro Help
Replies
0
Views
398
Alvaroro84
A
E
  • Question Question
Open vba in current sheet
Replies
2
Views
444
Excelexcel86
E
E
  • Question Question
Share point vba
Replies
0
Views
520
Excelexcel86
E

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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