Excel. List of files on my FTP

Hello

With FSO and Shell:

Option Explicit Option Private Module ' ' Note: you must enable references (in Tools > References ...) to: ' - Microsoft Scripting Runtime ' - Microsoft Shell Controls And Automation ' Public Sub Lister_Fichiers() ' Lists files in a directory and its subdirectories in an Excel worksheet ' The information stored is: ' - file name, ' - full path, ' - directory, ' - creation date, ' - last access date, ' - last modification date, ' - size, ' - attributes. ' ' Date Developer Action ' ------------------------------------------------------------------------------------------- ' 14/06/10 Patrice Version 1.0.2 ' Dim objShell As Shell32.Shell 'Shell Dim objChoix As Shell32.Folder 'Folder selection search Dim wbkRapport As Excel.Workbook 'Result workbook ' Dim rngPlage As Excel.Range 'Generic range ' Dim strChemin As String 'Folder path ' Dim strMsg As String 'Dialog message ' Const WINDOW_HANDLE = 0 Const OPTIONS = 513 'sous dossiers système et sans le bouton Nouveau dossier On Error Resume Next 'Show the dialog with the tree strMsg = "Choose the directory to analyze :" Set objShell = New Shell32.Shell Set objChoix = objShell.BrowseForFolder(WINDOW_HANDLE, strMsg, OPTIONS) strChemin = objChoix.Items.Item.Path 'strChemin = objChoix.Self.Path 'If the path is valid If strChemin <> "" Then Application.Interactive = False '- stop screen refresh and calculations Application.Cursor = xlWait Application.Calculation = xlCalculationManual Application.ScreenUpdating = False '- add a new workbook Set wbkRapport = Application.Workbooks.Add(xlWBATWorksheet) Set rngPlage = wbkRapport.Worksheets(1).Range(Cells(1, 1), Cells(1, 10)) '- write the column headers With rngPlage .Font.Bold = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Cells(1, 1).Formula = "File concerned" .Cells(1, 2).Formula = "Creation date" .Cells(1, 3).Formula = "Date last accessed" .Cells(1, 4).Formula = "Date of last modification" .Cells(1, 5).Formula = "File size in KB" .Cells(1, 6).Formula = "File type" .Cells(1, 7).Formula = "Extension" .Cells(1, 8).Formula = "Attributes" .Cells(1, 9).Formula = "Path to the file" .Cells(1, 10).Formula = "Full file path" .Columns.AutoFit End With '- list the folder tree Call ListerDossier(strChemin, wbkRapport) '- restore screen refresh and calculations Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.Cursor = xlDefault Application.Interactive = True End If Set objShell = Nothing Set objChoix = Nothing End Sub Private Sub ListerDossier(strChemin As String, wbkRapport As Excel.Workbook) ' Recursive procedure that lists the folder tree (and subfolders) ' ' Arguments : strChemin [in] Path of the folder to explore ' wbkRapport [in] Report file ' ' Date Developer Action ' ------------------------------------------------------------------------------------------- ' 14/06/10 Patrice Version 1.0.2 ' Dim objFSO As FileSystemObject ' File System Object Dim objRep As Scripting.Folder ' Folder to analyze Dim objSubRep As Scripting.Folders ' Subfolders collection ' Dim objSubRepItem As Scripting.Folder ' Subfolder ' Dim objSubFile As Scripting.Files ' Files collection ' Dim objSubFileItem As Scripting.File ' File to search ' Dim rngPlage As Excel.Range ' Generic range ' Dim strAtt As String ' File attributes ' Dim n°L As Integer ' Row number to write on the worksheet ' Dim att As Integer ' File attributes value ' Dim adr As String Dim ctr As Integer On Error Resume Next ' Explore folder Set objFSO = New FileSystemObject Set objRep = objFSO.GetFolder(strChemin) 'folder Set objSubRep = objRep.SubFolders 'sous-dossiers '- process each subfolder For Each objSubRepItem In objSubRep Call ListerDossier(objSubRepItem.Path, wbkRapport) 'recursive call Next Set objSubFile = objRep.Files 'files '- process each file For Each objSubFileItem In objSubFile '-- assign the attributes names att = objSubFileItem.Attributes strAtt = "" If att = 0 Then strAtt = "None" If att And 8 Then strAtt = strAtt & "V " 'Volume If att And 16 Then strAtt = strAtt & "D " 'Directory If att And 1 Then strAtt = strAtt & "R" 'Read Only If att And 2 Then strAtt = strAtt & "H" 'Hidden If att And 4 Then strAtt = strAtt & "S" 'System If att And 32 Then strAtt = strAtt & "A" 'Archive If att And 1024 Then strAtt = strAtt & " Alias" If att And 2048 Then strAtt = strAtt & " Compressed" '-- write the line to the worksheet Set rngPlage = wbkRapport.Worksheets(1).Range(Cells(1, 1), Cells(1, 10)) Set rngPlage = rngPlage.Offset(wbkRapport.Worksheets(1).UsedRange.Rows.Count) With rngPlage adr = .Address .Cells(1, 1).Formula = objSubFileItem.Name .Cells(1, 2).Formula = objSubFileItem.DateCreated .Cells(1, 3).Formula = objSubFileItem.DateLastAccessed .Cells(1, 4).Formula = objSubFileItem.DateLastModified .Cells(1, 5).Formula = Arrondi(objSubFileItem.Size / 1024, 0) .Cells(1, 5).HorizontalAlignment = xlCenter .Cells(1, 6).Formula = objSubFileItem.Type .Cells(1, 7).Formula = objFSO.GetExtensionName(objSubFileItem.Name) .Cells(1, 7).HorizontalAlignment = xlCenter .Cells(1, 8).Formula = strAtt .Cells(1, 8).HorizontalAlignment = xlCenter .Cells(1, 9).Formula = objSubFileItem.ParentFolder .Cells(1, 10).Formula = objSubFileItem.Path ' .Offset(1 - .Row).Resize(.Row).Columns.AutoFit End With Next If Not rngPlage Is Nothing Then rngPlage.Offset(1 - rngPlage.Row).Resize(rngPlage.Row).Columns.AutoFit Set rngPlage = Nothing End If Set objFSO = Nothing Set objRep = Nothing Set objSubRep = Nothing Set objSubRepItem = Nothing Set objSubFile = Nothing Set objSubFileItem = Nothing End Sub Private Function Arrondi(ByVal Nombre, ByVal Decimales) ' Replaces the VBA Round() function which behaves poorly for numbers like 2a + 0.5 (rounded down!!!) ' ' Arguments : Nombre [in] Number to round & Decimales [in] Number of decimals ' ' Date Developer Action ' ------------------------------------------------------------------------------------------- ' 28/08/06 Patrice Version 2.0 ' Arrondi = Int(Nombre * 10 ^ Decimales + 1 / 2) / 10 ^ Decimales End Function

Best regards
Patrice

No one can possess knowledge, that’s why we share it.

Hello Patrice,

I create files that are used on many workstations.
Users access them via an online opening file.
This opening file downloads the common files onto their machines.
Currently it’s not great because each time it opens, it downloads all the files.
I want them to download only the modified files.
I had written a macro that wasn’t great either since it loaded all the files to compare dates and only saved the modified ones, it was slow and not reliable.
So this list will allow comparison and downloading only the necessary files.
The problem is that I cannot intervene on the different machines to enable tools or Microsoft references, especially since some are on 7 and others on 8.

Am I clear enough? In any case, thank you very much, I manage a bit with Excel but I don’t understand anything about external links (important: all users are using Excel 2016).

You are super nice, but at 70 I’m surely unable to understand lol
I can’t find where to enter my .com name and the directory to list?
should I enter the password somewhere?
I’ve been looking everywhere and can’t find it