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.
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