MsOf07 User Input Directed Search Of Folders and SubFolders, Copy File To Different Folder-VBForums
Results 1 to 15 of 15

Thread: User Input Directed Search Of Folders and SubFolders, Copy File To Different Folder

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    User Input Directed Search Of Folders and SubFolders, Copy File To Different Folder

    I have searched for my answer, looked at little pieces here and there, but haven't hit the nail on the head.

    Everyday we have 4 files (2 AM, and 2 PM), both divided into two different work locations. We need to archive those 4 files into our workgroups local drive. Now each day the filename contains that days date. So for today AM we have: Time Exception A 4-7-2012 and Time Exception B 4-7-2012, and PM we have Time Exception A 4-7-2012 and Time Exception B 4-7-2012. All four files are located in different folders. Folder paths:

    AM:
    Time Exception A 4-7-2012:
    S:\ABCSharedFolders\OOC\spOvA\ES\ABC\PSD\AM Time Exception\PP #7\

    Time Exception B 4-7-2012:
    S:\ABCSharedFolder\OOC\spOvA\ES\ABC\BSD\AM FILES\AM Time Exception\PP-07\

    PM:
    Time Exception A 4-7-2012
    S:\ABCSharedFolders\OOC\spOvA\ES\ABC\PSD\PM Time Exception\PP 7-12\

    Time Exception B 4-7-2012
    S:\ABCSharedFolders\OOC\spOvA\ES\ABC\BSD\PM FILES\PM Time Exceptions\PP #07 - 2012\

    One problem that we run into as you can see is that the consistancy is lost when these workgroups name their folders. That is why having some code that will search the subdirectories is critical since we do have consistancy all the way up to PSD and BSD folders.

    Once we can confirm that the file exists, it will have to be copied and pasted to our workgroups folder. The path for this is:

    AM:
    Time Exception A 4-7-2012:
    Z:\ABC\ABC Field\COSA\Time Exception Archive\2012 Archive\A\April\AM\

    Time Exception B 4-7-2012:
    Z:\ABC\ABC Field\COSA\Time Exception Archive\2012 Archive\B\April\AM\

    PM:
    Time Exception A 4-7-2012
    Z:\ABC\ABC Field\COSA\Time Exception Archive\2012 Archive\A\April\PM\

    Time Exception B 4-7-2012
    Z:\ABC\ABC Field\COSA\Time Exception Archive\2012 Archive\B\April\PM\

    If possible, we would like the "user input" or search function to only look for the date in the filename just in case someone gets the need to save the file with a funky name, at least we know the date will always be there.

    Here is what I have found so far. I know it is nowhere near what I need, but overall I liked the functionallity of it. It will only search for specific, whole, filenames and that we wont have.

    Code:
    Sub CopySomeFiles() 
        Dim FSO, sourceFolder, currentFile, filesInSourceFolder 
        Dim strSourceFolderPath As String 
        Dim strDestinationFolderPath As String 
        Dim strUserInput As String 
        Set FSO = CreateObject("Scripting.FileSystemObject") 
     
        ' Figure out which file to copy from where to where 
        strUserInput = InputBox("Please enter name of file to copy.") 
        strSourceFolderPath = "C:\MySourceFolder" 
        strDestinationFolderPath = "C:\MyDestinationFolder" 
     
        Set sourceFolder = FSO.GetFolder(strSourceFolderPath) 
        Set filesInSourceFolder = sourceFolder.Files 
     
        ' Look at all files in source folder. If name matches, 
        ' copy to destination folder. 
        For Each currentFile In filesInSourceFolder 
            If currentFile.Name = strUserInput Then 
                currentFile.Copy (FSO.BuildPath(strDestinationFolderPath, _ 
                    currentFile.Name)) 
            End If 
        Next 
     
    End Sub
    I hope I explained this in a manner that can be followed. It is a lot of information. Please let me know if I can elaborate any more.

  2. #2

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    My apologies, I forgot to mention that the files to be copied are word document files, either .doc or .docx. We are applying this macro to our Excel Spreadsheet though.

    Thanks

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,808

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    i would look at using the searchtreeforfile API to locate the correct file regardless of folder
    copying the file to correct destination is then straightforward
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    Thanks westconn1 for your reply. I am not familiar with the searchtreeforfile API, so I'll have to look it up. Any pointers in the meantime?

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    Or can anyone else chime in?

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,925

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    So if I understand you correctly, you simply want to know if a file exists in a subdirectory?
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    Quote Originally Posted by koolsid View Post
    So if I understand you correctly, you simply want to know if a file exists in a subdirectory?
    I need to confirm they are in the subdirectory, and then copy them to our workgroups specified folder.

  8. #8
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,925

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    >>>I need to confirm they are in the subdirectory, and then copy them to our workgroups specified folder.

    Would something like these help?

    Topic: Search directory and sub folders for Excel files

    Link: http://www.vbaexpress.com/kb/getarticle.php?kb_id=800

    OR

    Topic: Recursive Folder Search - Find Files in Subdirectories

    Link: http://www.ammara.com/access_image_f...er_search.html

    You need to of course amend the code to suit your needs

    Using those functions you check check if the file file exists or not and then use the FILECOPY command, simply copy it to the relevant folder

    HTH

    Sid
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,808

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    try like
    vb Code:
    1. Private Const MAX_PATH As Long = 260
    2.          
    3. Private Declare Function SearchTreeForFile Lib "imagehlp" _
    4. (ByVal sRootPath As String, _
    5.  ByVal InputPathName As String, _
    6.  ByVal OutputPathBuffer As String) As Boolean
    7.  
    8. sub copyfromtree()
    9.  
    10.     sfname = Space(MAX_PATH)
    11.  
    12.     If SearchTreeForFile("S:\ABCSharedFolder\OOC\spOvA\ES\ABC", "Time Exception A 4-7-2012" , sfname) Then
    13.        sfname = Left(sfname, InStr(sfname, vbNullChar) - 1)
    14.        filecopy sfname, destfile
    15.     end if
    16. end sub
    you may need to supply more filepath, to differenciate am and pm files, and destfile is a fully qualified path to the destination file

    you could store some part or the file path in an array for the different files then use a loop, els build the copy into a procedure, that can be called from the main code
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    Quote Originally Posted by westconn1 View Post
    try like
    vb Code:
    1. Private Const MAX_PATH As Long = 260
    2.          
    3. Private Declare Function SearchTreeForFile Lib "imagehlp" _
    4. (ByVal sRootPath As String, _
    5.  ByVal InputPathName As String, _
    6.  ByVal OutputPathBuffer As String) As Boolean
    7.  
    8. sub copyfromtree()
    9.  
    10.     sfname = Space(MAX_PATH)
    11.  
    12.     If SearchTreeForFile("S:\ABCSharedFolder\OOC\spOvA\ES\ABC", "Time Exception A 4-7-2012" , sfname) Then
    13.        sfname = Left(sfname, InStr(sfname, vbNullChar) - 1)
    14.        filecopy sfname, destfile
    15.     end if
    16. end sub
    you may need to supply more filepath, to differenciate am and pm files, and destfile is a fully qualified path to the destination file

    you could store some part or the file path in an array for the different files then use a loop, els build the copy into a procedure, that can be called from the main code
    I cannot get that code to work, even when I add the filepath. Is more required that I should know, or should that have worked?

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,808

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    Is more required that I should know, or should that have worked?
    it should have just worked (tested) as far as retrieving a full path to file, but destfile (destination file path\name) would have to be assigned a value for the filecopy to work

    the first 6 lines must be at the top of the code pane, not within some procedure

    I cannot get that code to work
    what happens?
    error? wrong result? nothing?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    Quote Originally Posted by westconn1 View Post
    it should have just worked (tested) as far as retrieving a full path to file, but destfile (destination file path\name) would have to be assigned a value for the filecopy to work

    the first 6 lines must be at the top of the code pane, not within some procedure

    what happens?
    error? wrong result? nothing?
    Sorry for the late reply, just had another chance to run it at work. It seems to be doing nothing. When the sub is run, the hourglass comes up for a split second and then its gone. No errors, but no results. I have even specified the SearchTreeForFile all the way to the bottom folder and still nothing.

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,808

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    is the destination file created?

    insert at line 14
    vb Code:
    1. msgbox sfname
    if the file is not found, you should get empty messagebox, else the full path of the file should be in the messagebox
    even without the messagebox the filecopy statement would produce an error if the file is not found by searchtreeforfile
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    Quote Originally Posted by westconn1 View Post
    is the destination file created?

    insert at line 14
    vb Code:
    1. msgbox sfname
    if the file is not found, you should get empty messagebox, else the full path of the file should be in the messagebox
    even without the messagebox the filecopy statement would produce an error if the file is not found by searchtreeforfile
    I did as you suggested and entered:
    Code:
    MsgBox sfname, destfile
    in line 14 and it seems like it's doing something, but then the code stops running and nothing comes up. No message box, no error.

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,808

    Re: User Input Directed Search Of Folders and SubFolders, Copy File To Different Fold

    just in case it was a network thing, i tested on a network drive, it worked fine, but was slower
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.