CODEWITHSUNDEEP
excelvba
academic_dwarf
academic_dwarf

Reputation: 23

Moving files to a subfolder based on partial filename in VBA without using FSO

This code is run from a macro-enabled workbook. It checks if there is already any files in the same folder as the workbook which names start with "repositorio nóminas" and moves them to a subfolder.

It uses the method "Name _ As _" to move them, but the "* . *" argument does not seem to work in this context. How can I check for partial name while using the "Name _ As _" method?

Dim wb      As Workbook  
Dim path    As String

Set wb = ActiveWorkbook    
path = wb.path & "\"

If path & "\repositorio" & "*.*" = True Then            !!!Error 13 Type Mismatch!!!
    Name path & "\repositorio" & "*.*" As path & "backups\" & "\repositorio" & "*.*"
End If

For clarification, I got the idea of trying the "* . *" argument from this piece of code I had found in the past, where it uses "path" and the argument to find anything that exists in that path.

str = Dir(path & "*.*")

Do While str <> ""
    ctr = ctr + 1
    str = Dir
 Loop

Upvotes: 1

Views: 148

Answers (1)

FunThomas
FunThomas

Reputation: 29511

As already written, you need to rename your files one by one. So the logic is: Loop over all files that matches the filename pattern and execute the name command.

To loop over all files in a folder, you can use the Dir-command (or use the FileSystemObject). The logic of the Dir-command is the following: You issue an initial Dir with a filename pattern (including the path). You will get the name of the first matching file as result. To get the next matching file, you issue another Dir-command, but without parameters. As long as there are matching files, Dir` will return the name. When there is no file left, it returns an empty string.

Note that the filename that is returned by Dir does not contain the path, only the file name.

So your code would look like that

Const FilenamePattern = "repositorio*.*"
Dim path as String, backupPath as String
path = wb.path & "\"
backupPath = path & "backups\"

Dim filename as string
filename = Dir(path & FilenamePattern)   ' Get first file
Do While filename <> ""
    ' Move the file to backup folder
    Name path & filename As backupPath & filename
    filename = Dir                       ' Get next file, if any
Loop

Upvotes: 1

Related Questions