Moving a Sub-folder and its contents from one location to another location - both paths specified in a table, referred to by ActiveCell & offset

[dougmarkham]

Hi Folks,

Basic goal:

To move a folder and its contents into another folder that already exists.

Detail:

I wish to use VBA to move a single folder called 13) Customer Template (and all its contents including folders) from its current folder-path S:\Projects (5)\13) Customer Template\ to a new location S:\COMPLETED\ i.e., so the new folder-path is S:\COMPLETED\13) Customer Template\.

To do this, I wish to refer to the ToPath and FromPath via reference to the ActiveCell (red text below) i.e., if the ToPath is in the ActiveCell, the VB code will refer to the FromPath using Offset from the ActiveCell (the FromPath is in the same row, one column to the left).

FromPath	ToPath
S:\Projects (5)\13) Customer Template\	S:\COMPLETED\

I have the following code below:

Sub Move_Rename_Folder()

    'This example move the folder from FromPath to ToPath.
    Dim fso As Object
    Dim CurrentFrom As Range
    Dim CurrentTo As Range
    Dim FromPath As String
    Dim ToPath As String
    
    Set CurrentTo = Application.ActiveCell
    Set CurrentFrom = Application.ActiveCell.Offset(0, -1)
    
    FromPath = CurrentFrom.Value
    ToPath = CurrentTo.Value
    'Note: It is not possible to use a folder that exist in ToPath

    If Right(FromPath, 1) = "\" Then
        FromPath = Left(FromPath, Len(FromPath) - 1)
    End If

    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If

    Set fso = CreateObject("scripting.filesystemobject")
    If fso.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    If fso.FolderExists(ToPath) = True Then
        MsgBox ToPath & " exists, not possible to move to an existing folder"
        Exit Sub
    End If

    fso.MoveFolder Source:=FromPath, Destination:=ToPath
    MsgBox "The folder is moved from " & FromPath & " to " & ToPath
 End Sub

When I run this code, I get the MsgBox: "S:\COMPLETED\" exists, not possible to move to an existing folder".

When I use the intended folderpath---S:\COMPLETED\13) Customer Template\ --- as the ToPath (in the ActiveCell), I get the error message: Run-time error '5': Invalid procedure call or argument.

Although I've moved files before, I'm not familiar with moving folders and their contents.
Would you please help me trouble-shoot this code?

Kind regards,

Doug.

P.S. in References, tools: Microsoft Scripting Runtime is ticked.

 

[John_w]

When I run this code, I get the MsgBox: "S:\COMPLETED\" exists, not possible to move to an existing folder".

Surely you want the destination folder to exist, so this should display a message and exit if the destination folder doesn't exist.

If you look at the help for MoveFolder

MoveFolder method (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

it says: "If ... destination ends with a path separator, it is assumed that destination specifies an existing folder in which to move the matching files".

Therefore the destination path must end with "\", however your code removes it.

Try this:

Public Sub Move_Folder()

    Dim fso As Scripting.FileSystemObject
    Dim FromPath As String
    Dim ToPath As String
    
    FromPath = Application.ActiveCell.Value
    ToPath = Application.ActiveCell.Offset(0, -1).Value

    If Right(FromPath, 1) = "\" Then FromPath = Left(FromPath, Len(FromPath) - 1)
    If Right(ToPath, 1) <> "\" Then ToPath = ToPath & "\"
    
    'Set fso = CreateObject("scripting.filesystemobject")
    Set fso = New Scripting.FileSystemObject
    If fso.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    If fso.FolderExists(ToPath) = False Then
        MsgBox ToPath & " doesn't exist"
        Exit Sub
    End If

    fso.MoveFolder Source:=FromPath, Destination:=ToPath
    MsgBox FromPath & " moved to " & ToPath
    
End Sub

 

[dougmarkham]

Surely you want the destination folder to exist, so this should display a message and exit if the destination folder doesn't exist.

If you look at the help for MoveFolder

MoveFolder method (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

it says: "If ... destination ends with a path separator, it is assumed that destination specifies an existing folder in which to move the matching files".

Therefore the destination path must end with "\", however your code removes it.

Try this:

Public Sub Move_Folder()

    Dim fso As Scripting.FileSystemObject
    Dim FromPath As String
    Dim ToPath As String
   
    ToPath = Application.ActiveCell.Value
    FromPath = Application.ActiveCell.Offset(0, -1).Value

    If Right(FromPath, 1) = "\" Then FromPath = Left(FromPath, Len(FromPath) - 1)
    If Right(ToPath, 1) <> "\" Then ToPath = ToPath & "\"

    'Set fso = CreateObject("scripting.filesystemobject")
    Set fso = New Scripting.FileSystemObject
    If fso.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    If fso.FolderExists(ToPath) = False Then
        MsgBox ToPath & " doesn't exist"
        Exit Sub
    End If

    fso.MoveFolder Source:=FromPath, Destination:=ToPath
    MsgBox FromPath & " moved to " & ToPath
   
End Sub

Hi John_w,

Thanks for replying!

I tried this code and it has worked !
Thanks for spotting the below error, I had forgotten that there needs to be a "\" for folderpaths in these types of macros.

    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If

Many thanks for your help!

Kind regards,

Doug.