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

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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! :)
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
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
 
Solution

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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.

 

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,303
Messages
5,623,865
Members
415,997
Latest member
ragomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top