Find the latest version

[Nlhicks]

Set Wb = Workbooks.Open("WAPA-UGPR Facility Rating and SOL Record (Data File)_v159.xlsx")
Wb.Sheets("Facility Ratings & SOLs (Lines)").Activate
Set Sheet2 = Sheets("Facility Ratings & SOLs (Lines)")
Set Ws = Sheet2.UsedRange

This code will take the Wb variable and set it equal to the latest version of the workbook but I am telling it to set it equal to the exact one. How can I make this more generic so that it will set it equal to the last version saved? When I close the workbook it automatically up revs to the next version so it would become 160. This code will not work on version 160 it will only work on 159. How can I word it so that it will work on whatever version I am wanting to make changes to?

 

[NdNoviceHlp]

Hi Nlhicks. The following code will give you the complete file path of the most recently saved .xlsx file in some specified folder. Not sure if that's really what you're after? Dave

Function NewestFile(FlderName As String) As String
Dim objfso As Object, objfolder As Object, objfile As Object
Dim NewFileName As String, NewFileDate As String, Temp As String
'returns newest .xlsm file in inputted folder
'ie. Call NewestFile("c:\users\dlsfa\documents\datafiles\")
Set objfso = CreateObject("scripting.filesystemobject")
Set objfolder = objfso.GetFolder(FlderName)
NewFileDate = vbNullString
For Each objfile In objfolder.Files
If InStr(objfile.Name, ".xlsx") Then
Temp = objfile.DateCreated
If Temp > NewFileDate Then
NewFileDate = Temp
NewFileName = objfile.Path 'Name
End If
End If
Next objfile
'MsgBox "Newest File: " & NewFileName & " created " & NewFileDate
NewestFile = NewFileName
Set objfolder = Nothing
Set objfso = Nothing
End Function

To operate eg....

Call NewestFile("c:\users\dlsfa\documents\datafiles\")

[

 

[Nlhicks]

This is perfect thank you

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave

 

[Nlhicks]

Is there any way I can make my macro look at the latest version of a workbook and do what I need it to do to that workbook? Everytime I save the workbook it will create a new version number which is fantastic however, my macro will forever look at version v158 unless I can write it to look at the latest version. I am not sure how to do that, any ideas?

 

[NdNoviceHlp]

I guess I should have added how you could use it. You need to adjust the folder path to suit. HTH. Dave

Dim wb As Workbook
Set wb = Workbooks.Open(NewestFile("c:\users\dlsfa\documents\datafiles\"))
'or... Workbooks.Open Filename:=NewestFile("c:\users\dlsfa\documents\datafiles\")
'or.. Dim NewFile as string
'.....NewFile = NewestFile("c:\users\dlsfa\documents\datafiles\")
'.....MsgBox NewFile

 

[Nlhicks]

Perfect thank you I will give it a shot.

 

[NdNoviceHlp]

You are once again welcome. Have a nice day. Dave