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

Board Regular
Joined
Jul 19, 2016
Messages
231
Office Version
  1. 365
Platform
  1. Windows
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).

FromPathToPath
S:\Projects (5)\13) Customer Template\S:\COMPLETED\

I have the following code below:

VBA Code:
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,703
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


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:
VBA Code:
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

Board Regular
Joined
Jul 19, 2016
Messages
231
Office Version
  1. 365
Platform
  1. Windows
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


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:
VBA Code:
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.
VBA Code:
    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If

Many thanks for your help!

Kind regards,

Doug.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top