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

[NessPJ]

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:

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:

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......

 

[gallen]

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

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]

usage:
range("A2").select
getValInAllFilesInDir "c:\temp"


​

'-------------
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

 

[NessPJ]

Thanks for the help guys, its working now. I'm using ranman256's solution.