• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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

0
Anthony6890
Asked:
Anthony6890
  • 7
  • 5
  • 2
  • +1
1 Solution
 
Bill PrewCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 PrewCommented:
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 PrewCommented:
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
 
Anthony6890Author Commented:
Ok I get a Loop without Do error.
0
 
Bill PrewCommented:
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 PrewCommented:
Welcome.

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now