Can someone help me figure out why this syntax no longer works when I change the file path?

Can someone please look at this syntax and tell me why when I update the file path to a new folder for 2012 it will no longer work?  I used to get the latest file created- date and time, but right now I'm getting nothing.

I hope someone can help me.
Sub getLatestFileBlueFT()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim fPath As String
Dim fName As String, aFile As Object
Dim FSO As Object
Dim lastFile As String
Dim lastFileCreatedDate As Date


        
    Set wkb = ThisWorkbook
    Set wks = ThisWorkbook.Sheets("BlueFT")
    
    fPath = wks.Range("A1").Value
    
    On Error GoTo errHandler
    
    fName = Dir(pathname:=fPath & "\*.*") 'list all files
        
    If fName <> "" Then
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Do
            fName = Dir
            
            If fName <> "" Then
                Set aFile = FSO.GetFile(fPath & "\" & fName)
                If lastFileCreatedDate < aFile.DateCreated Then 'first pass lastFileCreationDate = 0 so no need to initialize
                    lastFile = fName
                    lastFileCreatedDate = aFile.DateCreated 'or use DateLastModified to get last modified date
                End If
            End If
        Loop While fName <> ""

        wks.Range("A7").Value = lastFile
        wks.Range("A8").Value = lastFileCreatedDate
    
        Set aFile = Nothing
    Else
        wks.Range("A7").Value = "No Files Found in " & fPath & " directory"
        wks.Range("A8").Value = "No Files Found in " & fPath & " directory"
    End If

errHandler:
    If Err.Number <> 0 Then
        MsgBox "Error in DIR function - possibly your path is incorrectly entered.  Try again", vbCritical, "Error:  Path not found"
    End If
    
    Set aFile = Nothing
    
End Sub

Open in new window

LVL 1
Anthony6890Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
What used to be in A1, and what is it now?

~bp
0
lee555J5Commented:
Have you checked to make sure your path is correct in A1? Do you have the correct drive mappings? Are there actually files in that location?

I ask because I don't see anything wrong with the code, especially if nothing's changed between last year and now.

Lee
0
Anthony6890Author Commented:
in A1 before was:   H:\BlueFT\2011

now it's H:\BlueFT\2012

The only difference is that there is only one file in there now because we only have January's file submitted.  

My path for A1 is definitely correct.  
0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Kent DyerIT Security Analyst SeniorCommented:
I am not connecting the dots here..

See you have:

=fPath & "\*.*"

fPath is the Celll A1

Do not see the patch like C:\ or D:\ but would have to assume that is driven by a button click or macro..

HTH,

Kent
0
Anthony6890Author Commented:
Sorry yes it is- it's driven by a button.  I will attach the spreadsheet so you can look at it.   Provider-Summary---Copy.xlsm
0
Anthony6890Author Commented:
Also realize, that when the path was for the 2011 folder, there was more than one file in it.  Right now there is only one file in the folder, and I feel like that might be the issue.  
0
lee555J5Commented:
That shouldn't matter; but just for kicks, add 2-3 junk files and see?
0
Anthony6890Author Commented:
Ahhhh- I put two junk files in and now I have a new file name and a creation date... so it has to do with the fact that it's the only file.  It must be in the check for the creation date...
0
Bill PrewIT / Software Engineering ConsultantCommented:
The way the code is written, you seem to skip over the first file, gotten by the DIR() outside the loop:

    fName = Dir(pathname:=fPath & "\*.*") 'list all files
    If fName <> "" Then
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Do
            fName = Dir

~bp
0
Anthony6890Author Commented:
bp,

How do you suggest I fix that then?
0
Bill PrewIT / Software Engineering ConsultantCommented:
Take a look at this, worked it up quick but hopefully gets you close, didn't test the code locally though.

Sub getLatestFileBlueFT()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim rng As Range
    Dim fPath As String
    Dim fName As String, aFile As Object
    Dim FSO As Object
    Dim lastFile As String
    Dim lastFileCreatedDate As Date
        
    Set wkb = ThisWorkbook
    Set wks = ThisWorkbook.Sheets("BlueFT")
    
    fPath = wks.Range("A1").Value
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    On Error GoTo errHandler
    
    fName = Dir(pathname:=fPath & "\*.*") 'list all files
    lastfile = ""
        
    Do While fName <> ""
        Set aFile = FSO.GetFile(fPath & "\" & fName)
        If lastFileCreatedDate < aFile.DateCreated Then 'first pass lastFileCreationDate = 0 so no need to initialize
            lastFile = fName
            lastFileCreatedDate = aFile.DateCreated 'or use DateLastModified to get last modified date
        End If
        Set aFile = Nothing

        fName = Dir
    Loop
        
    If lastfile <> "" Then
        wks.Range("A7").Value = lastFile
        wks.Range("A8").Value = lastFileCreatedDate
    Else
        wks.Range("A7").Value = "No Files Found in " & fPath & " directory"
        wks.Range("A8").Value = "No Files Found in " & fPath & " directory"
    End If

    Set FSO = Nothing

errHandler:
    If Err.Number <> 0 Then
        MsgBox "Error in DIR function - possibly your path is incorrectly entered.  Try again", vbCritical, "Error:  Path not found"
    End If
    
    Set aFile = Nothing
    
End Sub

Open in new window

~bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony6890Author Commented:
Ok I get a Loop without Do error.
0
Bill PrewIT / Software Engineering ConsultantCommented:
Check for a paste error or something, I just pasted it in to Excel here and it worked without error.

~bp
0
Anthony6890Author Commented:
Hey BP, it worked like a charm!

Thanks for all your help, I really appreciate it.  

-AM
0
Bill PrewIT / Software Engineering ConsultantCommented:
Welcome.

~bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.