Copying File Names from folder to Workbook

MistakesWereMade

Board Regular
Joined
May 22, 2019
Messages
103
So I have this code, and I would like to adjust it so that instead of running the script until there are no files left to be copied, I would like for the code to continue its loop (purposefully) for at least 500 or more times WHILE inserting/clearing cells in the column it was formerly copying file names into. Right now, this code allows me to perfectly copy file names BUT once it runs out of files to copy, it stops inserting file names into the column because the script has stopped. This poses a problem when a file is deleted from the folder it is copying from, as you basically gain one or more extra copied file names in the copied column. If you add a file, this isn't a problem because it increases its column length with copied file names. I am trying to completely automate copying process with a single click to update the file names in a folder, and want to fix the issue of having inaccurate results when a file is deleted from the source folder. Setting a fixed number of loops for the code would be fine rather than waiting for "" to occur. The number of files in a folder will never reach a huge value so 500 is more than enough.

Hopefully this makes sense... I am a total noob at VBA, but am familiar with coding logic...
Below is the working code.

Code:
Private Sub CommandButton1_Click()


  Dim strTargetFolder As String, strFileName As String, nCountItem As Integer


  '  Initialization
  nCountItem = 2
  strTargetFolder = "C:\Users\MyUser\Desktop\My Files" & "\"
  strFileName = Dir(strTargetFolder, vbDirectory)


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

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello MistakesWereMade,

This will clear column "D" starting at row 2 down to the last entry in the column.

Code:
Private Sub CommandButton1_Click()

    Dim strTargetFolder As String, strFileName As String, nCountItem As Integer
    Dim RngBeg  As Range
    Dim RngEnd  As Range

    ' Clear any previous file names in the column "D"
    Set RngBeg = Range("D2")
    Set RngEnd = Cells(Rows.Count, "D").End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub Else Range(RngBeg, RngEnd).ClearContents
    
    '  Initialization
    nCountItem = 2
    strTargetFolder = "C:\Users\MyUser\Desktop\My Files" & "\"
    strFileName = Dir(strTargetFolder, vbDirectory)

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

MistakesWereMade

Board Regular
Joined
May 22, 2019
Messages
103
Awesome! It works exactly the way I want it to thanks to you! I did have one debug in the if statement you created where it should say,
RngEnd.Row > RngBeg.Row and so forth. Anyways, the code works so well. Much thanks Leith!

Here is my final code.

Code:
Private Sub CommandButton1_Click()


  Dim strTargetFolder As String, strFileName As String, nCountItem As Integer
  Dim RngBeg  As Range
  Dim RngEnd  As Range
  
  ' Clear any previous file names in column "D"
  Set RngBeg = Range("D2")
  Set RngEnd = Cells(Rows.Count, "D").End(xlUp)
  If RngEnd.Row < RngBeg.Row Then Exit Sub Else Range(RngBeg, RngEnd).ClearContents


  '  Initialization
  nCountItem = 2
  strTargetFolder = "C:\Users\Kyle\Desktop\My Files" & "\"
  strFileName = Dir(strTargetFolder, vbDirectory)


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

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello MistakesWereMade,

Sorry about the typo. Great work on catching and correcting it. Glad I could help.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,764
Messages
5,638,226
Members
417,016
Latest member
Tegguy

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