1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open Excel File with partial name

Discussion in 'VBA Macros' started by rubikscube991, Dec 6, 2018.

  1. rubikscube991

    rubikscube991 New Member

    Messages:
    25
    HI everyone.. Thanks for the attention. I searched everywhere but unfortunately couldn't find a solution. Either the macros written are too long or complex for my understanding.
    problem : I have a macro in 1 workbook and from that, i need to open another workbook, kept at some location.
    Cell C2 contains the folder location & C3 has the file name. Below macro works if the Filename is exactly same.
    But problem is the file name updates on daily basis something like : "DCI_V_DOC_REGISTER_DD_MM_YYYY". I want to keep the file name as "DCI_V_DOC_REGISTER" only, so that i dont have to bother about the rest of the filename. I tried adding "*" at file end but its not working. pls help.

    Code (vb):

    Sub Open_register()
       
        Sheet1.Rows("2:4").Select
        Selection.EntireRow.Hidden = True
     
     
        a = Sheet1.Range("C2") & "\"
        b = Sheet1.Range("C3").Value
     
           Workbooks.Open Filename:=a & b
     
    End Sub
     
    thanks in advance.
    Last edited by a moderator: Dec 6, 2018
  2. Ajesh

    Ajesh Active Member

    Messages:
    153
    Try this:-

    Code (vb):

    Sub OpenFile()
        Dim wb As Workbook
        Dim Path, KeyWord As String
        Dim nFile, myFile As String
        Dim fExt As String
       
        Set wb = ThisWorkbook
        fExt = "*.xls*"
       
        With wb.Sheets("Sheet1")
            Path = .Range("C2") & Application.PathSeparator
            KeyWord = .Range("C3").Value
        End With
       
        nFile = Dir(Path & fExt)
           
        Do While nFile <> ""
            DoEvents
            If nFile Like "*" & KeyWord & "*" Then
                myFile = Path & nFile
                Workbooks.Open Filename:=myFile
                Exit Sub
            Else
                nFile = Dir
                nFile = ""
            End If
        Loop

    End Sub
     
    rubikscube991 likes this.
  3. rubikscube991

    rubikscube991 New Member

    Messages:
    25
    thanks a lot for the code.. really grateful to you ajesh.._/\_

    its working absolutely fine..
    I have two questions..

    1) Would it be possible to copy the full file name of opened file name in cell D3?
    2) Can u pls. explain in what scenario Do while & DoEvents would be required.. I am not able to understand?

    thanks
  4. Ajesh

    Ajesh Active Member

    Messages:
    153
    1) add this line below Workbook open:
    wb.Sheets("Sheet1").Range("D3").Value = ActiveWorkbook.Name
    If you need file name with complete path use ActiveWorkbook.FullName

    2) DoEvents passes control from excel to the operating system. Control is returned after the operating system has finished processing the events in its queue.

    3) Do While is loop used to continue doing same thing till a condition is true.

    Thanks/Ajesh
    Last edited: Dec 6, 2018

Share This Page