moving folder and contents without using FSO

[RhodEvans]

Afternoon all,

I have been searching for a way of moving folders (& contents) via an excel macro. The folders are all in the same directory that the excel file is in and I want to make certain folders move to a "removed" folder that is in the same parent directory. The issue I am having is that all the examples I find use FSO (FileSystemObject) which have been disabled on the works machines.
Does anyone know if it is even possible without this, if so any suggestions?

As always I appreciate any help/advice that you are able to give!!

Rhod

 

[Rick Rothstein]

I have been searching for a way of moving folders (& contents) via an excel macro. The folders are all in the same directory that the excel file is in and I want to make certain folders move to a "removed" folder that is in the same parent directory. The issue I am having is that all the examples I find use FSO (FileSystemObject) which have been disabled on the works machines.
Does anyone know if it is even possible without this, if so any suggestions?

What can you tell us about the names of the folders that you want to move? Do you have a list of them somewhere (where)? Do they all begin with the same text (what is it)? It is hard to write code when the specifics are unknown, so whatever you can tell us about the names of the folders that will allow you to identify them, the better.

 

[taurean]

If command prompt is fine then it can be used via VBA.

 

[RhodEvans]

The folder names are all of a format Surname, First name and the ones that need moving are all in the column B(Surname), A(Firstname) starting in row 4 or a worksheet named "removed". The contents of the folders may change, but will always have at least the following two files in.

Surname, Firstname - Feedbacks.xls
Surname, Firstname - Feedbacks PPR.doc

Hopefully this is the information you needed, if not I will try to give you some more

 

[RhodEvans]

No command prompt. If it helps it is a Citrix remote desktop system

 

[Rick Rothstein]

The folder names are all of a format Surname, First name and the ones that need moving are all in the column B(Surname), A(Firstname) starting in row 4 or a worksheet named "removed". The contents of the folders may change, but will always have at least the following two files in.

Surname, Firstname - Feedbacks.xls
Surname, Firstname - Feedbacks PPR.doc

Hopefully this is the information you needed, if not I will try to give you some more

My assumptions from reading your messsages... the "removed" directory is in the same directory as the excel file containing the macro which is also the same directory that the name directories are in. Also, the name directories have a comma/space between the surname and first name. Give this macro a try...

Sub MoveDirectoriesToRemoveDirectory()
  Dim X As Long, LastRow As Long, FileName As String
  With Sheets("removed")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    For X = 4 To LastRow
      FileName = .Cells(X, "B").Value & ", " & .Cells(X, "A").Value
      Name ThisWorkbook.Path & "\" & FileName As ThisWorkbook.Path & "\removed\" & FileName
    Next
  End With
End Sub

 

[RhodEvans]

Reading back my original question/request I'm not sure if I explained the directory structure very well . So here goes, hopefully I can clarify it a little bit:

The Workbook that is running the code is called: "HR 1:1 waiting list"
It is in a folder called: New Waiting list
The folders to move are also in: New waiting list
The folder I am trying to move them to will be called: Removed and is: also in the folder: New Waiting list

Hopefully that helps for the relative directory structure.

Sorry if I am confusing things!

 

[Rick Rothstein]

Reading back my original question/request I'm not sure if I explained the directory structure very well . So here goes, hopefully I can clarify it a little bit:

The Workbook that is running the code is called: "HR 1:1 waiting list"
It is in a folder called: New Waiting list
The folders to move are also in: New waiting list
The folder I am trying to move them to will be called: Removed and is: also in the folder: New Waiting list

Hopefully that helps for the relative directory structure.

Sorry if I am confusing things!

That is what I assumed (I did not need to know the parent directory name specifically since I had the workbook tell it to me via the ThisWorkbook.Path call). As long as the names are located on a worksheet named "removed" and in the order and location specified in your original message, the code I posted should work directly for you. Give it a try and let me know.

 

[RhodEvans]

Thank you very much. I'm slowly getting better at VBA. No where near as good as you (and doubt that I'll ever have your knowledge). But one of the best way's I've found to learn is from seeing solutions on here and also attempting to help others. So you help is invaluable.
Thank you again!

 

[Rick Rothstein]

Thank you very much. I'm slowly getting better at VBA. No where near as good as you (and doubt that I'll ever have your knowledge). But one of the best way's I've found to learn is from seeing solutions on here and also attempting to help others. So you help is invaluable.
Thank you again!

Can I assume from your comment that my code worked for you?