Open workbook new method

[LeonardSterk]

Hello,

I had a working macro that would open the most recent excel file. I made this macro at home for work. Now it turns out that this macro doesn't work at my work because we don't have permission to do this
The macro says we don't have permission, and no, I'm can't/not allowed to change permissions

Sub Open()
    Workbooks.Open Split(CreateObject("WScript.Shell").Exec("cmd /c dir /b/o-d/s D:\Pro\Files\*.xls*").StdOut.ReadAll, vbCr)(0)
End Sub

Anyone know some alternative code to find the most recent file in D:\Pro\Files\ ?

 

[AC PORTA VIA]

Try this

Sub openLastModified()
Dim FolderPath As String, tableName As String, latestTblName As String
Dim latestModified As Date, modifiedDate As Date


FolderPath = "D:\Pro\Files\"


tableName = Dir(FolderPath & "*.xls")


Do While tableName <> vbNullString
    modifiedDate = FileDateTime(FolderPath & tableName)
    If latestModified < modifiedDate Then
        latestModified = modifiedDate
        latestTblName = tableName
    End If
    tableName = Dir()
Loop


Workbooks.Open FolderPath & latestTblName


End Sub

 

[LeonardSterk]

Try this

Sub openLastModified()
Dim FolderPath As String, tableName As String, latestTblName As String
Dim latestModified As Date, modifiedDate As Date


FolderPath = "D:\Pro\Files\"


tableName = Dir(FolderPath & "*.xls")


Do While tableName <> vbNullString
    modifiedDate = FileDateTime(FolderPath & tableName)
    If latestModified < modifiedDate Then
        latestModified = modifiedDate
        latestTblName = tableName
    End If
    tableName = Dir()
Loop


Workbooks.Open FolderPath & latestTblName


End Sub

YOU ROCK!!!
Unbelievable that worked first try.
Take that annoying company security !! >

 

[AC PORTA VIA]

You're welcome
glad it works for you

 

[LeonardSterk]

You're welcome
glad it works for you

Yes it did, thank you.

-nevermind-

 

[LeonardSterk]

You're welcome
glad it works for you

I pasted some of my code below yours, it ends with closing the workbook but it asks if I want to save the file.
How can I make it in vbs to save the workbook?
I tried ActiveWorkbook.Save but that didn't work

Sub openLastModified()
Dim FolderPath As String, tableName As String, latestTblName As String
Dim latestModified As Date, modifiedDate As Date


FolderPath = "D:\Pro\Files\"


tableName = Dir(FolderPath & "*.xls")


Do While tableName <> vbNullString
    modifiedDate = FileDateTime(FolderPath & tableName)
    If latestModified < modifiedDate Then
        latestModified = modifiedDate
        latestTblName = tableName
    End If
    tableName = Dir()
Loop


Workbooks.Open FolderPath & latestTblName

`mycode
ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub

 

[AC PORTA VIA]

So you want to open last modified file and then save and close same file?

 

[AC PORTA VIA]

if that is what you want just add this below

ActiveWorkbook.Close True