+ Reply to Thread
Results 1 to 30 of 30

search for files based on prefix and moves to specific folder

  1. #1
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    search for files based on prefix and moves to specific folder

    hello
    every month I attach files (PDF,XLSX) in this directory C:\Users\PC Leopard\Downloads . the attached PDF extensions files contains names MN-2022-00010 , MN-2022-00011 ......
    & the attached XLSX extensions files contains names Bridgestone Sales Report 04 APR. 2022,Bridgestone Sales Report 05 Mar. 2022, .so what I want create two folders for files PDF & XLSX files thier names are PURCHASE_PDF & SALES & RETURNS_XLSX and create folders months into them . for instance create folder REPORT_APRIL based on current month and move the files contain "MN" in the first part (MN-2022-00010 , MN-2022-00011 ......) from C:\Users\PC Leopard\Downloads to C:\Users\PC Leopard\Downloads\PURCHASE_PDF\REPORT_APRIL . as to folder SALES & RETURNS_XLSX create folder REPORT_APRIL based on current month and move the files contain MONTH
    (04 APR) ( Bridgestone Sales Report 04 APR) from C:\Users\PC Leopard\Downloads to C:\Users\PC Leopard\Downloads\SALES & RETURNS_XLSX\REPORT_APRIL

    thanks
    Last edited by abdo meghari; 04-23-2022 at 06:34 AM.

  2. #2
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    is there any help?

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Question Re: search for files based on prefix and moves to specific folder


    Hello,

    the source directory contains only .pdf & .xlsx files or may contain other files types ?

    Give some examples with accurate & complete filenames

  4. #4
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    Hi Marc L,
    just PDF & XLSX
    Give some examples with accurate & complete filenames
    I gave you in OP
    the XLSX : Bridgestone Sales Report 04 APR. 2022,Bridgestone Sales Report 05 Mar. 2022
    the PDF: MN-2022-00010 , MN-2022-00011

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Question Re: search for files based on prefix and moves to specific folder


    You forgot the extension : with or without a space between the name and the extension ?

    The Excel workbooks have a date reference within their filenames but not the pdf files so no month subfolder into the destination ?

  6. #6
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    You forgot the extension : with or without a space between the name and the extension ?
    there is no space between the name and the extension
    so no month subfolder into the destination ?
    actually I wish create month subfolder automatically instead of create manually and moves the files if you can do that will be great.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Question Re: search for files based on prefix and moves to specific folder


    I could but you must be accurate in order to avoid some disaster

    In your source directory you gave some files examples for March like for April :
    so the VBA procedure must process each pdf and each xlsx files or only some specific ones according to the current month ?
    For example in April only the *APR. XXXX.xlsx file should be moved but not the March file
    or both must be moved to their relative destination directory ?

    In case a file to be moved already exists in the destination directory :
    the file in the destination directory can be deleted / overwritten or the VBA procedure must be conservative doing nothing ?

  8. #8
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    so the VBA procedure must process each pdf and each xlsx files or only some specific ones according to the current month ?
    any file contains prefix name as in above whether xlsx or pdf should move them both into subfolder month
    the file MAR . XXXX.xlsx, xxxx.pdf should move to MAR subfolder, the file APR. XXXX.xlsx, XXXX.pdf should move to APRIL subfolder
    In case a file to be moved already exists in the destination directory :
    should be overwritten

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow Re: search for files based on prefix and moves to specific folder


    Ok only for the Excel workbooks but not for the pdf files as there is no month reference within their names
    or you forgot to well elaborate something

  10. #10
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    or you forgot to well elaborate something
    actually I don't forgot it , but need your advice how could avoid this problem , can depends on created or modified date? , any suggestion you have ?

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Question Re: search for files based on prefix and moves to specific folder


    It's your duty to well elaborate as I won't guess anything ! Now you know why you did not get any answer

    So if the pdf filenames have no month reference so the modified date should be the reference ?

    The 'prefix' works only for pdf files aka MN-*.pdf but not for Excel workbooks or am I missing something ?
    Last edited by Marc L; 05-02-2022 at 04:21 AM.

  12. #12
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    so the modified date should be the reference ?
    it's ok
    so what could be such 'prefix' on your side ?
    I mean starting the files names for XLSX Bridgestone Sales Report ,as to PDF MN

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow Re: search for files based on prefix and moves to specific folder


    Well my bad so the prefix MN-*.pdf and Briggestone Sales Report *.xlsx must be harcoded within the VBA procedure
    in order any other pdf / xlsx files can't be moved !

  14. #14
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    must be harcoded within the VBA procedure
    how? I don't understand this.

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow Re: search for files based on prefix and moves to specific folder


    You want a VBA procedure specific to move only the pdf files starting with 'MN-' and
    only the Excel workbooks starting with 'Briggestone Sales Report' so both hardcoded, not variable

  16. #16
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    if you see this is very complicated to achieve that , is there any altenative?

  17. #17
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Question Re: search for files based on prefix and moves to specific folder


    No it was just a need to confirm what you are expecting for ?

    Another point : as your profile does not indicate your platform I hope it is only for Windows ?

  18. #18
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    ok I will update it
    Last edited by AliGW; 05-02-2022 at 05:28 AM. Reason: PLEASE don't quote unnecessarily!

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,987

    Re: search for files based on prefix and moves to specific folder

    @abdo meghari

    Until you answer the questions asked, a solution cannot be offered. Please address ALL of Marc L's queries. He has not told you that this is impossible, complicated or anything of that sort.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  20. #20
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow Re: search for files based on prefix and moves to specific folder

    Quote Originally Posted by abdo meghari View Post
    ok I will update it
    I can help only if for Windows, just confirm

  21. #21
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    He has not told you that this is impossible, complicated or anything of that sort.
    it's up to him if he want to answer me or not

    solution cannot be offered. Please address ALL of Marc L's queries
    I gave him last detials in post#12 I no know if he needs more .

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,987

    Re: search for files based on prefix and moves to specific folder

    @abdo meghari

    Don't be so rude. We are all trying to help you. Mind your manners, please.

    You have not answered the question about platform: is this for Windows or Mac?

  23. #23
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    Don't be so rude
    sorry
    You have not answered the question about platform: is this for Windows or Mac?
    windows not Mac and i don't find choice in edited profile to updating

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,987

    Re: search for files based on prefix and moves to specific folder

    See below.
    Attached Images Attached Images

  25. #25
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    I thought there is different option to updating . thank for this

  26. #26
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Cool Try this


    For starters a VBA demonstration to paste to the top of a module
    for Excel 2010 & upper Windows versions only whatever 32 or 64 bits (for previous Excel versions just remove PtrSafe) :

    PHP Code: 
    Private Declare PtrSafe Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal DirPath$) As Boolean

    Sub Demo1
    ()
      Const 
    "C:\Users\PC Leopard\Downloads\"
        Dim F(), N, L&, V, C%, Bill$
      ReDim F(1 To 1)
            N = Dir(P)
      While N > ""
         If N Like "
    MN-*.pdf" Or N Like "Briggestone Sales Report ## ???. ####.xlsx" Then L = L + 1: ReDim Preserve F(1 To L): F(L) = N
            
    Dir
      Wend
            
    If 0 Then Beep: Exit Sub
            V 
    = Array("SALES & RETURNS_XLSX""PURCHASE_PDF")
        For 
    Each N In F
                C 
    N Like "*.pdf"
                
    If C Then L Fix(FileDateTime(N)) Else DateValue(Mid(N266))
                
    Bill V(-C) & "\REPORT_" Evaluate("UPPER(TEXT(" ",""[$-409]mmmm\\""))") & N
            
    If MakeSureDirectoryPathExists(BillThen
                
    If Dir(Bill) > "" Then Kill Bill
                Name P 
    As Bill
            
    Else
                
    Debug.Print Bill
            End 
    If
        
    Next
            Application
    .Speech.Speak "Done !"
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !

  27. #27
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    @Marc L thanks but the code just work for files PDF the XLSX nothing happends .

  28. #28
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Re: search for files based on prefix and moves to specific folder


    If the Excel workbooks are not moved that means they are not found so just check the file mask in the While block

  29. #29
    Forum Contributor
    Join Date
    12-02-2020
    Location
    Asia
    MS-Off Ver
    2010 & 2016 insider(Windows 10 64-bit)
    Posts
    441

    Re: search for files based on prefix and moves to specific folder

    you're right . it was a typo in the code Briggestone should be Bridgestone
    many thanks for your time & patience and solution

  30. #30
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Thumbs up Re: search for files based on prefix and moves to specific folder


    My bad ! Thanks for the rep' !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy hyperlink content to folder and add prefix name to the files
    By mikephie in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-14-2020, 01:48 AM
  2. prefix folder name to files with excel vba
    By druva in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-25-2019, 01:47 PM
  3. Replies: 1
    Last Post: 01-28-2019, 06:55 PM
  4. [SOLVED] VBA code to add a prefix in all pdf files in a folder
    By UPA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-31-2018, 01:53 AM
  5. VBA to search files within a folder for a specific string and return file name
    By tjej6987 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-03-2014, 12:41 PM
  6. [SOLVED] How to put Prefix in filenames on all files in folder with Modified Date
    By junmishima in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-26-2013, 08:47 PM
  7. Search files in folder based on search string
    By djvice in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-22-2008, 01:12 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1