VBA - List last accessed & last modified date (including subfolders)

[Carterland]

Hi All,

I've looked around but I can't seem to find a working code that does ALL of the bits I need. I have seen some with parts of it and others that seem to have it all but for whatever reason (likely my fault) I cant get it to work.

So I'm looking for a VBA code that will look through a file path, then look in all subfolders within the file path and return the details required for a file within each of the subfolders named 'LWDB'.

So the folder structure is:

Main folder path followed by 5 subfolders named: 1, 2, 3, 4, 5

Within each of those folders there is a file names LWDB

I need the code to return the details for each of those 5 LWDB files.

I hope that makes sense. I have attached an image below to give you some idea. The subfolder numbers won't changes so Column A doesn't need amending in any way.

Thanks in advance

 

[DanteAmor]

Check if the following is what you need.
Change the initial folder on this line.

sPath = "C:\trabajo\" 'initial path folder

Sub FilesDates()
  Dim sPath As String, subFolder As String, sFile As String
  Dim att As Scripting.FileSystemObject
  Dim i As Long
 
  sPath = "C:\trabajo\"   'initial path folder
  
  Set att = CreateObject("Scripting.FileSystemObject")
  '
  For i = 1 To 5
    subFolder = sPath & i & "\"
    sFile = Dir(subFolder & "LWDB*")
    If sFile <> "" Then
      Cells(i + 1, "B") = sFile
      Cells(i + 1, "C") = att.GetFile(subFolder & sFile).DateCreated
      Cells(i + 1, "D") = att.GetFile(subFolder & sFile).DateLastAccessed
      Cells(i + 1, "E") = att.GetFile(subFolder & sFile).DateLastModified
    End If
  Next
End Sub

 

[Carterland]

Goo

Check if the following is what you need.
Change the initial folder on this line.

sPath = "C:\trabajo\" 'initial path folder

Sub FilesDates()
  Dim sPath As String, subFolder As String, sFile As String
  Dim att As Scripting.FileSystemObject
  Dim i As Long
 
  sPath = "C:\trabajo\"   'initial path folder
 
  Set att = CreateObject("Scripting.FileSystemObject")
  '
  For i = 1 To 5
    subFolder = sPath & i & "\"
    sFile = Dir(subFolder & "LWDB*")
    If sFile <> "" Then
      Cells(i + 1, "B") = sFile
      Cells(i + 1, "C") = att.GetFile(subFolder & sFile).DateCreated
      Cells(i + 1, "D") = att.GetFile(subFolder & sFile).DateLastAccessed
      Cells(i + 1, "E") = att.GetFile(subFolder & sFile).DateLastModified
    End If
  Next
End Sub

Good Morning,

Thanks for your code above.

I am getting an error when running this code, the error is 'User-defined type not defined'

The only change I made was to insert the file path, the error occurs at the start of the code.

Apologies if this is something I've done. Also (apologies again) I forgot to update my profile to office 365, not sure if that is related but just wanted to note.

Thanks again

update*

This is the line that seems to cause the error:

Dim att As Scripting.FileSystemObject

 

[Carterland]

Update 2**

So I managed to resolve the error above by enabling the microsoft scripting reference in the tools option (after a quick google)

So the code doesn't throw up any errors now however it also doesn't seem to do anything either. When I run the macro whilst not throwing up an errors, nothing appears in the cells.

Any ideas?

 

[DanteAmor]

enabling the microsoft scripting reference in the tools

That's correct, that reference was missing

___

You could put an image of your initial folder and where you can see the subfolders 1,2,3,4,5

And another image where I can see the file "LWDB"

 

[Marc L]

So I managed to resolve the error above by enabling the microsoft scripting reference in the tools option (after a quick google)

Hi, in fact this is not necessary as the code mixes early binding in the Dim statement and late binding in the Set att = codeline ‼​

​

So staying in late binding - without activating any reference - you just need to amend the Dim codeline as Dim att As Object and voilà !​

 

[DanteAmor]

Dim att As object

Thanks Marc, that is correct, but sometimes it is better to use the correct declaration to get "help" from the vba excel intellisense.
In this case I don't know all the names of the properties of the file.
And since the OP found the solution, then we can continue with what follows.

 

[Marc L]

​

So in order to avoid any glitch in early binding you do not need to use CreateObject statement …​

 

[Carterland]

Hi All,

Apologies, I've made a mistake as I totally forgot that the LWDB file is actually within another subfolder named 'Log'

So there's the main path, then 5 subfolders named 1,2,3,4,5 then within each of those 5 subfolders there's another subfolder named 'Log' and within each of those there is a file named 'LWDB' which is the file I require the details for.

So sorry about that, no idea how I missed it. Also, in regards to the comments above regarding the mixing of bindings, what changes need to be made to the code in addition to the updates I've just provided?

Thanks

 

[DanteAmor]

Try this:

Change the initial folder on this line.
sPath = "C:\trabajo\" 'initial path folder

Enabling the microsoft scripting reference in the tools option

In my tests I must Set att = CreateObject("Scripting.FileSystemObject"), So let's move on.

Sub FilesDates()
  Dim sPath As String, subFolder As String, sFile As String
  Dim att As Scripting.FileSystemObject
  Dim i As Long
  Set att = CreateObject("Scripting.FileSystemObject")
  sPath = "C:\trabajo\"   'initial path folder
  '
  For i = 1 To 5
    subFolder = sPath & i & "\" & "Log" & "\"
    sFile = Dir(subFolder & "LWDB*")
    If sFile <> "" Then
      Cells(i + 1, "B") = sFile
      Cells(i + 1, "C") = att.GetFile(subFolder & sFile).DateCreated
      Cells(i + 1, "D") = att.GetFile(subFolder & sFile).DateLastAccessed
      Cells(i + 1, "E") = att.GetFile(subFolder & sFile).DateLastModified
    End If
  Next
End Sub

So you should have a folder structure as follows: