Copying File Names from folder to Workbook

[MistakesWereMade]

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.

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

 

[Leith Ross]

Hello MistakesWereMade,

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

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

 

[MistakesWereMade]

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.

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]

Hello MistakesWereMade,

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