• 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...

  • 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

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:
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:
Try this:-

Code:
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
 
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
 
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:
Back
Top