PDA

View Full Version : Moving folders using VBA



kilikanzer
08-17-2012, 02:54 AM
Hi,

I would like to ask your kind help with the following issue:

I have created amount of folders by this script:

Private Sub CommandButton1_Click()

Dim R As Range
Dim RootFolder As String
RootFolder = "G:\TEAM\TEST"
For Each R In Range("B4:B1411")
If Len(R.Text) > 0 Then
On Error Resume Next
MkDir RootFolder & "\" & R.Text
On Error GoTo 0
End If
Next R
End Sub

So I need a macro which will move some of these folders by criteria in column C.

For example, if the text in cell C4 is "yes", the folder created by the text in B4 have to move in "G:\TEAM\TEST\positive"

If the text in cell C5 is "no", the folder created by the text in B5 have to move in "G:\TEAM\TEST\negative", etc.

Any advice, help is muchly appreciated.

Thank you !

Bob Phillips
08-17-2012, 03:39 AM
Private Sub CommandButton1_Click()

Dim R As Range
Dim RootFolder As String

RootFolder = "G:\TEAM\TEST"
For Each R In Range("B4:B1411")

If Len(R.Text) > 0 Then

If R.Offset(0, 1).Value = "yes" Then

Name R.Value As RootFolder & "\positive" & Mid$(R.Text, InStrRev(R.Text, "\"), 99)
Else

Name R.Value As RootFolder & "\negative" & Mid$(R.Text, InStrRev(R.Text, "\"), 99)
End If
End If
Next R
End Sub

kilikanzer
08-17-2012, 04:36 AM
Thank you!

Maybe its my bad but i have "Run-time error 5" in this row:

Name R.Value As RootFolder & "\positive" & Mid$(R.Text, InStrRev(R.Text, "\"), 99)

And i have to add that the cells in column "C" can be "yes", "no" or "empty". So i want to move only these folders (created from Range("B4:B1411")) which in Range("C4:C1411") are not "empty".

Thank you once again !

Bob Phillips
08-17-2012, 05:25 AM
Give me an example of what is in column B.

kilikanzer
08-17-2012, 06:26 AM
In column B there are a names (text format).

Simplified it's looking like that:

B
C
Folder name
Criteria

folder 1
yes
folder 2
yes
folder 3
yes
folder 4
folder 5
folder 6
no
folder 7
folder 8
no
folder 9
no
folder 10
yes
folder 11
folder 12
yes
folder 13
yes
folder 14
yes
folder 15

Bob Phillips
08-17-2012, 09:36 AM
You need to identify where those column B folders are located so as to move them. I had assumed they would be of the form x:\xxxx\xxxx so as to have a full path to rename. What is your path?

snb
08-17-2012, 09:36 AM
Private Sub CommandButton1_Click()
On Error Resume Next
mkdir "G:\TEAM\TEST"
mkdir "G:\TEAM\TEST\positive"
mkdir "G:\TEAM\TEST\negative"

For Each cl In Range("B4:B1411").specialcells(2)
MkDir "G:\TEAM\TEST\" & iif(cl.offset(,1)="Yes","positive\",iif(cl.offset(,1)="no","negative\","")) & cl.value
Next
End Sub

kilikanzer
08-20-2012, 12:24 AM
Thank you this is working well. But it don't solve the problem in fully. Because in this way the macro creates in the subdirectories new folders (same like in the main but divided by the criteria) and we have several same folders in the main and the subdirectories.

So already created (by macro1) folders in "G:\TEAM\TEST" should be moved (with all data in them) in "G:\TEAM\TEST\positive" or "G:\TEAM\TEST\negative" depending of criteria in column C.

In other words, already created folders in the main directory to be there as long as they haven't set criteria. And when they have "yes" or "not" to be moved in subdirectories with all files in them (by running macro2).

I am sorry for my unclear explanations.

i'll be very grateful for the solving of this problem.

Have a nice day!

Bob Phillips
08-20-2012, 12:37 AM
Private Sub CommandButton1_Click()

Dim R As Range
Dim RootFolder As String

RootFolder = "G:\TEAM\TEST"
For Each R In Range("B4:B1411")

If Len(R.Text) > 0 Then

If R.Offset(0, 1).Value = "yes" Then

Name RootFolder & "\" & R.Value As RootFolder & "\positive\" & R.Value
Else

Name RootFolder & "\" & R.Value As RootFolder & "\negative\" & R.Value
End If
End If
Next R
End Sub

kilikanzer
08-20-2012, 01:00 AM
I have Run-time error "53":
File not found

for this row:

Name RootFolder & "\" & R.Value As RootFolder & "\negative\" & R.Value

Bob Phillips
08-20-2012, 01:25 AM
That suggests that one (or more) of the folders in your list does not exist. Wrap it in On Error.

kilikanzer
08-20-2012, 04:27 AM
OK, it's working. But only for the first click on the macro button.

When i click for second time (on the first click folders are moved), run-time error 53 appears.

How can i avoid this and the macro to be looking only for new rows or new information in criteria column.

Bob Phillips
08-20-2012, 05:21 AM
Private Sub CommandButton1_Click()

Dim R As Range
Dim RootFolder As String

On Error Resume Next

RootFolder = "G:\TEAM\TEST"
For Each R In Range("B4:B1411")

If Len(R.Text) > 0 Then

If R.Offset(0, 1).Value = "yes" Then

Name RootFolder & "\" & R.Value As RootFolder & "\positive\" & R.Value
Else

Name RootFolder & "\" & R.Value As RootFolder & "\negative\" & R.Value
End If
End If
Next R
End Sub