Excel VB Help!!!

[mckhoa]

I have an excel sheet, column A and column B.

Column A consist of a list of names of folders
Column B consist of another set of list of names of folders

i.e.

ColumnA | ColumnB
1abc Washington
342a Oregon
and so on....

is there a code that I can write in VB that will copy all the files in folder "1abc" to folder "Washington"

Is this posible?

Thanks for any help.

 

[Smallman]

Hi Mchoa

RonDeBruin site he has a procedure which looks to do this.

http://www.rondebruin.nl/folder.htm

I have had a look at the procedure and adapted it (extracted the essence of the procedure). The following will do what you require.

Option Explicit
 
Sub MoveFolder()
    Dim FSO As Object
    Dim Paths As Variant
    Dim Rng As Range
 
    Set Paths = Sheet1.Range("A2", Range("A65536").End(xlUp))
 
    For Each Rng In Paths
        Set FSO = CreateObject("scripting.filesystemobject")
        FSO.CopyFolder Rng, Rng.Offset(0, 1)
    Next Rng
End Sub

I have run some tests on the above in the following file. As long as the path appears as it does in this worksheet and as long as it exists you should be all good.

Take care

Smallman

 

[mckhoa]

I notice that your file, it has the location of the file on the cell, how do I do it where the location of all the files are in one constant folder in the vb code.

 

[mckhoa]

One other thing, can I get it to copy only files and not folders?

 

[bobhc]

mckhoa

You have deleted a few of your own posts as in your words they where stupid questions. Get some coffee, look at your problem, think it through and then ask, you will get help but members are busy people and do not have time to look through every multiple question in posts.

 

[mckhoa]

thanks for the heads up, new to posting, I will definitely make a better effort to titling my needs.

 

[Smallman]

International time lines my friend the vaguaries of rest get in the road at time. Your first problem could have been solved a couple of ways. Thinking laterally you could have concatenated your file names with a constant folder path in cell. That would have given you a working file. Alternatively you could hard code it in the back end of vba. Here is an example.

Option Explicit
 
Sub MoveFolder()
    Dim FSO As Object
    Dim Paths As Variant
    Dim Rng As Range
    Const loc = "D:\"
 
    Set Paths = Sheet1.Range("A2", Range("A65536").End(xlUp))
 
    For Each Rng In Paths
        Set FSO = CreateObject("scripting.filesystemobject")
        FSO.CopyFolder loc & Rng.Value, loc & Rng.Offset(, 1).Value
    Next Rng
 
End Sub

I ran some tests on the above and it will move the folders within the file you mentioned as well. You will need to clarify this point.

Take care

Smallman