CODEWITHSUNDEEP
vbastringfilefilenamesmove
cdfj
cdfj

Reputation: 165

VBA move files based on string in filename

We receive Word files containing the ISO 3-letter country code as a string in the filename. We want to move these files from a holding folder to an existing folder for each country that is named by country name.

I have been going round in circles trying to get InStr to work. InStr is preferable, because the position of the string in the filename can change.

Could someone please advise what is wrong with my code? It runs, but does nothing.

Also there are a lot of country files (60+ ISO codes). Is there a smarter method than For Each and ElseIf?

Many thanks!

Sub MoveFiles_SpecificFolders()

Dim SrepFSO As FileSystemObject
Dim Srep As File
Dim HldFolder As Folder
Dim HoldingFolder As String
Dim TargetFolder As String
Dim Fname As String

Fname = (HoldingFolder & "*.doc*")

HoldingFolder = "C:\Users\Temp\Test\"
TargetFolder = "C:\Users\DifferentPath\MSfolders\"

Set SrepFSO = New Scripting.FileSystemObject
Set HldFolder = SrepFSO.GetFolder(HoldingFolder)

For Each Srep In HldFolder.Files

    If InStr(Fname, "ALB") <> 0 Then
    SrepFSO.MoveFile Source:=SrepFSO.GetFile(Srep), _
    Destination:=TargetFolder & "Albania\" & Srep.Name
    
    ElseIf InStr(Fname, "AND") <> 0 Then
    SrepFSO.MoveFile Source:=SrepFSO.GetFile(Srep), _
    Destination:=DestinationFolder & "Andorra\" & Srep.Name
    
    ElseIf InStr(Fname, "ARM") <> 0 Then
    SrepFSO.MoveFile Source:=SrepFSO.GetFile(Srep), _
    Destination:=DestinationFolder & "Armenia\" & Srep.Name
    
    End If
    
Next Srep

End Sub

Upvotes: 1

Views: 2020

Answers (2)

cdfj
cdfj

Reputation: 165

Corrected code with thanks to @BigBen and @Vityata.

Sub MoveFiles_SpecificFolders()

Dim SrepFSO As FileSystemObject
Dim Srep As File
Dim fso As New FileSystemObject
Dim HldFolder As Folder
Dim HoldingFolder As String
Dim TargetFolder As String

HoldingFolder = "C:\Users\xyz\Test_docs\"
TargetFolder = "C:\Users\xyz\MSfolders\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set SrepFSO = New Scripting.FileSystemObject
Set HldFolder = SrepFSO.GetFolder(HoldingFolder)

For Each Srep In HldFolder.Files

    If InStr(Srep, "ALB") <> 0 Then
    SrepFSO.MoveFile Source:=SrepFSO.GetFile(Srep), _
    Destination:=TargetFolder & "Albania\" & Srep.Name
    
    ElseIf InStr(Srep, "AND") <> 0 Then
    SrepFSO.MoveFile Source:=SrepFSO.GetFile(Srep), _
    Destination:=TargetFolder & "Andorra\" & Srep.Name
    
    ElseIf InStr(Srep, "ARM") <> 0 Then
    SrepFSO.MoveFile Source:=SrepFSO.GetFile(Srep), _
    Destination:=TargetFolder & "Armenia\" & Srep.Name
    
    ElseIf InStr(Srep, "GEO") <> 0 Then
    SrepFSO.MoveFile Source:=SrepFSO.GetFile(Srep), _
    Destination:=TargetFolder & "Georgia\" & Srep.Name
    
    End If
    
Next Srep

End Sub

Upvotes: 1

Vityata
Vityata

Reputation: 43595

Following your code this way:

Dim HoldingFolder As String
Dim TargetFolder As String
Dim Fname As String

Fname = (HoldingFolder & "*.doc*")

HoldingFolder = "C:\Users\Temp\Test\"

The Fname variable does not contain the part of the HoldingFolder. The HoldingFolder variable should be assigned before referred:

Dim HoldingFolder As String
Dim TargetFolder As String
Dim Fname As String
HoldingFolder = "C:\Users\Temp\Test\"

Fname = (HoldingFolder & "*.doc*")

Later in the For-Each loop, you would see a problem here -

If InStr(Fname, "ALB") <> 0 Then -> It should be If InStr(Srep, "ALB") <> 0 Then

Upvotes: 1

Related Questions