Copying file path from a cell?

[Dave_george]

I want to open the lastest file in a folder. The below code works. However I want to copy the file path from a cell range. Let me know if what I'm trying to do is logical.

Option Explicit

Sub NewestFile()

    Dim MyPath As String

    Dim MyFile As String

    Dim LatestFile As String

    Dim LatestDate As Date

    Dim LMD As Date

    MyPath = "H:\Operations\DC\DM\Reports\"

    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    MyFile = Dir(MyPath & "*.xls", vbNormal)

    If Len(MyFile) = 0 Then

        MsgBox "No files were found...", vbExclamation

        Exit Sub

    End If

    Do While Len(MyFile) > 0

        LMD = FileDateTime(MyPath & MyFile)

        If LMD > LatestDate Then

            LatestFile = MyFile

            LatestDate = LMD

        End If

        MyFile = Dir

    Loop

    Workbooks.Open MyPath & LatestFile

End Sub

 

[RoryA]

Change this:

MyPath = "H:\Operations\DC\DM\Reports\"

to this:

MyPath = Sheets("Sheetname").Range("A1").value

amending the sheet name and cell to match your setup.

 

[Dave_george]

I tried that code before and it gave me run-time error. Getting rid of quotation marks solved the problem.