Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    search directory and copy to folder (Excel 2000)

    Hello, after reading the posts about folder creation I though Id ask my question

    I have a list of about 600 search folders each a row in one column each row being a seperate search item.

    example spread sheet attached

    if I could run a search that could find where the each folder is located on the c: (directory) and write the director beside each search item in column B on the first run and then on the second copy each seach item to a folder called manip.

    I dont know how to do this but though it could be possible

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: search directory and copy to folder (Excel 2000)

    I am not sure I understand. Could you elaborate on what you have and what you want?

    Some general questions (there may be more depending on the answers, though elaboration, may also clear them up)
    Are the names in col A folder names, file names or both? Are they full or partial names?

    What happens if more than 1 match is found on the hard drive? What do you want in B (or should be quit searching after the first is found?)

    Where is "manip"? and what exactly do you want copied into this folder?

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search directory and copy to folder (Excel 2000)

    What do you want to do if a folder with the specified name exists in more than one folder on the disk. In other words, what should the code do if there is a Cir1666444 and a Cir2666444? What should the copy program do if the folder C:manip already contains the folder to be copied?
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search directory and copy to folder (Excel 2000)

    Thanks for the reply,

    The Column names are folders, they hold a .xls file each

    IF there is more then two instances take the first one found skip it.
    There should not be more then one instance though



    C:CustSamples2005 'search for folders here

    c:custtechmanip 'where the manip folder is

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search directory and copy to folder (Excel 2000)

    I think i answered your question in steves

    thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: search directory and copy to folder (Excel 2000)

    Does this do what you want?

    Steve

    <pre>Option Explicit
    Dim FSO As Object
    Sub SearchCopy()
    Dim sPath As String, sTo As String
    Dim rList As Range, rCell As Range
    Dim sTemp As String, sNotFound As String
    sPath = "C:CustSamples2005"
    sTo = "C:Custtechmanip"
    sNotFound = "Not Found"
    Set rList = Range(Range("a2"), Range("A65536").End(xlUp))
    Set FSO = CreateObject("Scripting.FileSystemObject")

    For Each rCell In rList
    With rCell
    sTemp = GetPath(sPath, .Value)
    If sTemp = "" Then
    .Offset(0, 1).Value = sNotFound
    Else
    .Offset(0, 1).Value = sTemp
    FSO.CopyFolder sTemp, sTo
    End If
    End With
    Next

    Set FSO = Nothing
    Set rCell = Nothing
    Set rList = Nothing
    End Sub

    Function GetPath(ByVal sPath As String, ByVal sSearch As String)
    Dim oFolder, Folder
    Set oFolder = FSO.GetFolder(sPath)
    For Each Folder In oFolder.subFolders
    If UCase(Folder.Name) = UCase(sSearch) Then
    GetPath = Folder.Path
    GoTo ExitHandler
    End If
    Next

    For Each Folder In oFolder.subFolders
    GetPath = GetPath(Folder, sSearch)
    If GetPath <> "" Then GoTo ExitHandler
    Next
    GetPath = ""
    ExitHandler:
    Set Folder = Nothing
    Set oFolder = Nothing
    End Function
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •