VBA to find / open latest / newest file stopped working

[edwardj3]

Afternoon,

Hope the forum can help. I've had a macro running successfully for a number of months which finds the latest / newest file and imports the data into a workbook. This month it has stopped identifying the latest/newest file which includes the string "TRAN_SUMMARY-M-". I took the code from something I found on the web months ago so to be honest I don't fully understand it. See my code below.

Tried again today using Excel 2007, Excel 2013 and Excel 2016 on Win 7 / 10 builds.


Option Explicit

Sub Phase_1()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim varDate As Variant
Dim strFind As String
Dim my_FileName As Variant
Dim SearchText As String
Dim GCell As Range
Dim c As Range
Dim SrchRng

Worksheets("Data_worksheet").Activate
Worksheets("Data_worksheet").Range("B1:L300").ClearContents

' Specify the folder….
strPath = "M:\Folder1\Folder2"

' Specify the word in the file name
strFind = "TRAN_SUMMARY-M-"


' Use Microsoft Scripting runtime
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

'Check data on each file in the folder
For Each objFile In objFolder.Files
If InStr(1, objFile.Name, strFind, vbTextCompare) Then
If objFile.DateLastModified > varDate Then
strName = objFile.Name
varDate = objFile.DateLastModified
End If
End If
Next 'objFile

my_FileName = strPath + "" + strName

'Opens the latest file identified above
If my_FileName <> False Then
Workbooks.Open Filename:=my_FileName
End If

' copy the source range
Range("A1:Z300").Select
Selection.Copy

' select current workbook and paste the values
ThisWorkbook.Activate
Sheets("Data_worksheet").Select
Sheets("Data_worksheet").Range("B1:L300").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Application.DisplayAlerts = False

' close the source workbook
Workbooks(strName).Activate
ActiveWorkbook.Close

Application.DisplayAlerts = True

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing

Set SrchRng = ActiveSheet.Range("B1", ActiveSheet.Range("B300").End(xlUp))
Do
Set c = SrchRng.Find("End of Report", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing


End Sub

When I add the code below after the 'Check data on each file in the folder code the message box does identify the latest / newest file so the path and string it is looking for is correct.

' Display file name in message box.
If Len(strName) = 0 Then
strName = "None found"
Else
strName = strName & " - is latest file - " & varDate
End If
MsgBox strName, , " Latest File"

Advice would be appreciated.

 

[NdNoviceHlp]

It looks like you're missing the backslash here...

my_FileName = strPath + "\" + strName

U need to ID the sheet here...

' copy the source range
 Range("A1:Z300").Select
 Selection.Copy

Also, U usually don't need to use selection... it slows things down...

Sheets("YourSheetName").Range("A1:Z300").Copy

HTH. Dave
ps. please use code tags

 

[edwardj3]

Thanks Dave. Checked my code and I do have
my_FileName = strPath + "" + strName

Thanks for the additional advice