Open File based on part of file name RRS feed

  • Question

  • I want to create a macro to do the following tasks.

    I have one Input folder and one Output folder.

    Macro would first open one file in Output folder.

    Let’s say the file name is “AB-CDE-All Online 10 Percent Online – Heavy” then the macro should be able to open a relevant input file from various input files. The logic is the input file for this output file is first four letters of this output file I.e AB-CDE-All Online hence the name of the input file in the input folder titled “AB-CDE-All Online” should open. The other logic to identify the input file would be any letters  before numeric digits (5, 10, 15 etc) which is again “AB-CDE-All Online”

    Please Help.


    Tuesday, December 4, 2012 3:27 PM

All replies

  • You can use operator Like if you know sentence, but if you do no, you should to make loop any file in Directory and using instr() function to compare the contents of the file name.

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Thursday, December 6, 2012 11:33 AM
  • If the format is always the same I would suggest:

    Dim strName As String
    Dim strText As String
    Dim vText As Variant
    strText = "AB-CDE-All Online 10 Percent Online - Heavy"
    vText = Split(strText, Chr(32))
    strName = vText(0) & Chr(32) & vText(1)
    MsgBox strName

    Graham Mayor - Word MVP

    Thursday, December 6, 2012 12:33 PM
  • Hi ,

    Thanks for your reply. I did not get the word "format". There are about 2100 files in the output folder and all are identical. There are total 75 files in input folder and each input file represents a file present in output folder. The example I have given above is just for one input file and one output file. The naming convention for each file present in both the folder are same, just the categories are different.. for example ..

    Output file: AB-CDE-All Online 10 Percent Online – Heavy .... Input file : AB-CDE-All Online

    Output file: FG-AVG- High Banner 15 Percent Online – Heavy   .... Input file : FG-AVG- High Banner

    Hope that helps. I know its quite confusing. Please let me know if you would like me to flash a more light on this.

    I really appreciate you are looking in to this.



    Thursday, December 6, 2012 10:35 PM
  • Try like this:

    sub yourprocedure()
    Dim Ob As Object, Plik As Object, Buf$
    const Yourdirectory$ = "C:\YourDirectory\" '<-change this
    Buf = Dir(Yourdirectory & "*.*") 
    Do While Len(Buf)
       Set Ob = CreateObject("Scripting.FilesystemObject")
       Set Plik = Ob.GetFile(Buf)
       if instr(1,"AB-CDE-All Online", then open_file(Yourdirectory & 'or whatever
       if instr(1,"FG-AVG- High Banner", then open_file(Yourdirectory & 
       Buf = Dir
    Set Ob = Nothing
    Set Plik = Nothing
    end sub
    sub open_file(byval yourfile$)
    dim wkb as workbook
    Set Wkb = Workbooks.Open(yourfile)
    'do sometnig
    wkb.close true
    end sub

    Or what ever you want to do with this file.

    To rename file you can use name commend:

    Name strSource As strDest

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, December 7, 2012 10:12 PM
  • Hi Oskar,

    Something is going wrong with my code, I'll explore and will let you know. Thanks soo much for your help so far ...



    Wednesday, December 19, 2012 11:41 PM
  • Ok, do not panic.

    Use [F8] i developer, to step by step thew a code line.

    Merry Xmas - Regards.

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Thursday, December 20, 2012 1:58 PM