[VBA] Check files and filedates within folder and list them

NessPJ

Active Member
Joined
May 10, 2011
Messages
385
Office Version
  1. 365
Hey guys,

I would like to create a simple tool in Excel that can list a few files in the specified folder together with the modified date in Excel.
Now i have 2 pieces of code that should do this, but i noticed that their approach seems different.
The first one will not work for me (it gives me a 'user defined type not defined' error), the second one works but does not scan the entire folder...it gets the modified date from a specified filename only.

This routine gives a user defined type not defined error on oFSO:
Code:
Private Sub Checkfiles()


Dim TargetSheet As String
Dim TargetCell As String
Dim Zoekpad As String


TargetSheet = Sheets("Parameters").Range("C6").Value
TargetCell = Sheets("Parameters").Range("C8").Value
Zoekpad = Sheets("Parameters").Range("C4").Value


Call ListFilesFSO(Zoekpad)




End Sub


Public Sub ListFilesFSO(ByVal sPath As String)


    Dim oFSO As FileSystemObject
    Dim oFolder As Folder
    Dim oFile As File


    Set oFSO = New FileSystemObject
    Set oFolder = oFSO.GetFolder(sPath)
    For Each oFile In oFolder.Files
        'Debug.Print oFile.Name
        Sheets(TargetSheet).Range(TargetCell).Value = oFile.Name
    Next 'oFile


    Set oFile = Nothing
    Set oFolder = Nothing
    Set oFSO = Nothing


End Sub

This routine only goes to a specific file:
Code:
Sub getdate()


Dim fileModDate As String


Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("\\192.168.1.4\test\testfile.txt")


fileModDate = f.DateLastModified


Sheets("Sheet1").Range("b2").Value = fileModDate


End Sub

I have a feeling the second routine works because it 'creates' the FileSystemObject as to where the first one only refers to it or something......
 
Last edited:

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,005
Hello,

I think this might point you in the right direction, it requires early binding. If late binding is a must it will need altering slightly

Code:
Sub GetDates()
'This sub relies on early binding (place a tick in 'Tools>References>Microsoft Scripting Runtime')
Dim fso As FileSystemObject
Dim fld As Folder
Dim file As file
Dim sPath As String 'path of folder




    sPath = "C:\Test\" 'change as required
    Set fso = New FileSystemObject
    
    Set fld = fso.GetFolder(sPath)
    For Each file In fld.Files
        Debug.Print file.Name & " - " & file.DateLastModified
    Next file
    
End Sub
 

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,900
usage:
range("A2").select
getValInAllFilesInDir "c:\temp"



Code:
'-------------
Private Sub getValInAllFilesInDir(ByVal pvDir)
'-------------
Dim FSO, oFolder, oFile, oRX, vProperty
Dim sTxt As String, sFile As String
Dim vFile
Const kMODdat = 5
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(pvDir)

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)  'use given folder
For Each oFile In oFolder.Files
  'If InStr(oFile.Name, ".xls") > 0 Then
       'vFile = oFile       'full path
       vFile = oFile.Name   'just name
       vProperty = oFile.DateLastModified
    
       ActiveCell.Offset(0, 0).Value = vFile
       ActiveCell.Offset(0, 1).Value = vProperty
       
       ActiveCell.Offset(1, 0).Select   'next row
  'End If
Next
Set oFile = Nothing
Set oFolder = Nothing
Set FSO = Nothing
End Sub
 
Last edited:

NessPJ

Active Member
Joined
May 10, 2011
Messages
385
Office Version
  1. 365
Thanks for the help guys, its working now. I'm using ranman256's solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,789
Messages
5,524,885
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top