Using VBA to read file names - need help altering code.

[rachel06]

Hello,

I'm trying to alter this code a bit to fit an existing template. I'm having issues with the following:

1. I need the files to be listed starting in cell B11. I have been able to get it to start in B1, but not B11
2. Am I able to add the file path starting in C11, File Type in D11, and Modified Date in E11? (Not super necessary, but it would just fit nicely in existing template)
3. Instead of having the file path in the code, can I have it reference a cell that has the file path in it? In the template, we currently drop the file path in cell C7 and the current macro reads that.

The current macro is very slow so I'm trying to alter this code because it is exponentially faster. Complete overhaul!

Sub ListAllFileNames()
Dim strTargetFolder As String, strFileName As String, nCountItem As Integer

' Initialization
nCountItem = 1
strTargetFolder = "file path" & "\"
strFileName = Dir(strTargetFolder, vbDirectory)


' Get the file name
Do While strFileName <> ""
If strFileName <> "." And strFileName <> ".." Then
Cells(nCountItem, 2) = strFileName
nCountItem = nCountItem + 1
End If
strFileName = Dir
Loop
End Sub

Any help is very much appreciated!

 

[DanteAmor]

Try this

Sub ListAllFileNames()
  Dim strTargetFolder As String, i As Long
  Dim fso As Object, sfolder As Object, strFileName As Object
  'Initialization
  strTargetFolder = Range("C7").Value
  i = 11
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set sfolder = fso.GetFolder(strTargetFolder)
  Set strFileName = sfolder.Files
  '
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  For Each strFileName In sfolder.Files
    Range("B" & i) = strFileName.Name
    Range("C" & i) = strTargetFolder
    Range("D" & i) = strFileName.Type
    Range("E" & i) = strFileName.DateLastModified
    i = i + 1
  Next
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

 

[rachel06]

This is really similar to the one we currently use that's very slow. The one I posted can grab 200+ files in less than a second. This one is incredibly slow. Any chance the one I posted can be altered?

Appreciate the response!

 

[DanteAmor]

This is an option to get the attributes of the files.
With the Dir function you can't read the attributes (that's why it's faster).
Let's hope if someone else provides you with another option.

File Object

docs.microsoft.com

 

[rachel06]

Oh! Good to know. Thanks for the details on this. I'm still trying to get a grasp on VBA and don't know the ins and outs of all the functions.