Getting a file listing (DIR)

[Asrampd]

This will have been asked a thousand times - But I can find no code that actually works. I have searched the internet for completed Spreadsheets - VBA code - advice, all without success. It seems to me that there is a gap in the available functions.


I need to get a listing of files in a named folder, into Excel 2007.

Ta.

 

[VoG]

See if this helps

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\example" ' <--------------- change to suit
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    j = j + 1
    Cells(j, 1).Value = MyFile
    MyFile = Dir
Loop
End Sub

 

[wigi]

What search engine did you use and what were your search terms?
This seems to me one of the easiest searches you can have.
If I'm not mistaken, the VBA Help files even show example code for Dir()
Did you use the search function of this Board?

 

[MARK858]

Another way. change the folder path in red to suit

Sub ListAllFile2007()
     
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
     
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add
    Set objFolder = objFSO.GetFolder("[COLOR=#ff0000]C:\Users\MARK858\Desktop\EXCEL FILES\[/COLOR]")
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:"
    For Each objFile In objFolder.Files
        ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
    Next
   Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
     
End Sub

 

[Asrampd]

Thanks for the replies, the purpose of the sheet I am doing is to document a Software installation and the additional files added by the user - Document files / adjustments of prefference / configuration........ I will keep backups and compare them. Therefore the info I need is like a DIR (in DOS), showing files incl. extensions, date, time, size. Listing subdirectories would also be good.

Ta.

 

[taurean]

If you use DOS then you can try [In DOS]
Goto the folder path using cd

dir /s>C:\DirList.txt

The bold part creates a file named DirList.txt on C Drive.

 

[MARK858]

You can also try some code Leith Ross created which has various options

[COLOR=darkblue]Sub[/COLOR] Test()


[COLOR=green]'***** Change the next string to point to your folder/direcgtory *****[/COLOR]
[COLOR=green]'Call ListFilesInFolder("C:\Users\?????\Documents\XL03\Mr Excel\", True)[/COLOR]
[COLOR=green]'[/COLOR]
'                      "C:\Users\?????\Documents\XL03\Mr Excel\"
[COLOR=green]'[/COLOR]
[COLOR=darkblue]Call[/COLOR] ListFilesInFolder("C:\Users\?????\Documents\XL03\Mr Excel\", [COLOR=darkblue]True[/COLOR])


[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]




Sub ListFilesInFolder([COLOR=darkblue]ByVal[/COLOR] SourceFolderName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], [COLOR=darkblue]ByVal[/COLOR] IncludeSubfolders [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR])
[COLOR=green]'[/COLOR]
' Leith Ross
[COLOR=green]' http://www.excelforum.com/excel-programming/645683-list-files-in-folder.html[/COLOR]
[COLOR=green]'[/COLOR]
' lists information about the files in SourceFolder
[COLOR=green]' example: ListFilesInFolder "C:\FolderName\", True[/COLOR]
[COLOR=green]'[/COLOR]
[COLOR=darkblue]Dim[/COLOR] Fso [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
[COLOR=darkblue]Dim[/COLOR] SourceFolder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
[COLOR=darkblue]Dim[/COLOR] Subfolder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
[COLOR=darkblue]Dim[/COLOR] FileItem [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
[COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
[COLOR=darkblue]Set[/COLOR] Fso = CreateObject("Scripting.FileSystem[COLOR=darkblue]Object[/COLOR]")
[COLOR=darkblue]Set[/COLOR] SourceFolder = Fso.GetFolder(SourceFolderName)
r = Range("A65536").End(xlUp).Row + 1
[COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] FileItem [COLOR=darkblue]In[/COLOR] SourceFolder.Files
  [COLOR=green]'display file properties[/COLOR]
  Cells(r, 1).Formula = FileItem.Name
  
  [COLOR=green]'***** Remove the single ' character in the below lines to see this information *****[/COLOR]
  [COLOR=green]'Cells(r, 2).Formula = FileItem.Path[/COLOR]
  [COLOR=green]'Cells(r, 3).Formula = FileItem.Size[/COLOR]
  [COLOR=green]'Cells(r, 4).Formula = FileItem.DateCreated[/COLOR]
  [COLOR=green]'Cells(r, 5).Formula = FileItem.DateLastModified[/COLOR]
  [COLOR=green]'Cells(r, 6).Formula = GetFileOwner(SourceFolder.Path, FileItem.Name)[/COLOR]
  
  r = r + 1 [COLOR=green]' next row number[/COLOR]
  X = SourceFolder.Path
[COLOR=darkblue]Next[/COLOR] FileItem
[COLOR=darkblue]If[/COLOR] IncludeSubfolders [COLOR=darkblue]Then[/COLOR]
  [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Subfolder [COLOR=darkblue]In[/COLOR] SourceFolder.subfolders
    ListFilesInFolder Subfolder.Path, [COLOR=darkblue]True[/COLOR]
  [COLOR=darkblue]Next[/COLOR] [COLOR=darkblue]Sub[/COLOR]folder
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]


[COLOR=green]'***** Remove the single ' character in the below lines to adjust the column windths[/COLOR]
[COLOR=green]'Columns("A:G").ColumnWidth = 4[/COLOR]
[COLOR=green]'Columns("H:I").AutoFit[/COLOR]
[COLOR=green]'Columns("J:L").ColumnWidth = 12[/COLOR]
[COLOR=green]'Columns("M:P").ColumnWidth = 8[/COLOR]


[COLOR=darkblue]Set[/COLOR] FileItem = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]Set[/COLOR] SourceFolder = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]Set[/COLOR] Fso = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=green]'ActiveWorkbook.Saved = True[/COLOR]
[COLOR=darkblue]End[/COLOR] Sub




[COLOR=darkblue]Function[/COLOR] GetFileOwner([COLOR=darkblue]ByVal[/COLOR] FilePath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], [COLOR=darkblue]ByVal[/COLOR] FileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR])
[COLOR=green]'[/COLOR]
' Leith Ross
[COLOR=green]' http://www.excelforum.com/excel-programming/645683-list-files-in-folder.html[/COLOR]
[COLOR=green]'[/COLOR]
[COLOR=darkblue]Dim[/COLOR] objFolder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
[COLOR=darkblue]Dim[/COLOR] objFolderItem [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
[COLOR=darkblue]Dim[/COLOR] objShell [COLOR=darkblue]As[/COLOR] Object
FileName = StrConv(FileName, vbUnicode)
FilePath = StrConv(FilePath, vbUnicode)
[COLOR=darkblue]Set[/COLOR] objShell = CreateObject("Shell.Application")
[COLOR=darkblue]Set[/COLOR] objFolder = objShell.Namespace(StrConv(FilePath, vbFromUnicode))
[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] objFolder [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
  [COLOR=darkblue]Set[/COLOR] objFolderItem = objFolder.ParseName(StrConv(FileName, vbFromUnicode))
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
[COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] objFolderItem [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
  GetFileOwner = objFolder.GetDetailsOf(objFolderItem, 8)
[COLOR=darkblue]Else[/COLOR]
  GetFileOwner = ""
[COLOR=darkblue]End[/COLOR] If
[COLOR=darkblue]Set[/COLOR] objShell = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]Set[/COLOR] objFolder = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]Set[/COLOR] objFolderItem = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR]

On a blank sheet...
Change the filepath and then run sub Test()

 

[Asrampd]

Mark858 - Sorry havent come back sooner - but Iv'e been playing with it. Just the ticket ! - I suppose it wouldn't be difficult to add a browse to the Sub - "Test"

Ta.

 

[MARK858]

I suppose it wouldn't be difficult to add a browse to the Sub - "Test"

Correct try a Google on "GetOpenFileName" or "

Application.FileDialog(msoFileDialogFilePicker)"​

 

[MARK858]

Sorry meant msoFileDialogFolderPicker