CODEWITHSUNDEEP
vbaexcel
Rafael Osipov
Rafael Osipov

Reputation: 740

VBA Open File From This Workbook's Folder Knowing Part of The Name

I am trying to open file from the same folder as the main workbook. The problem is that the name is not permanent and just one word stays always inside the name - "NAME".

I want to use specific method with Thisworkbook.Path to open the xlsx file but it is not finding the workbook with the code.

that is the relavant part of code:

Sub RemoveDuplicats()

Dim Harel As Workbook
Dim SAP As Workbook
Dim Path As String
Dim Found As String

Path = ThisWorkbook.Path
Found = Dir(Path & "*NAME*.xlsx")   'open SAP report
If Found <> "" Then
    Set SAP = Workbooks.Open(Path & Found)
End If

End Sub

Upvotes: 1

Views: 2877

Answers (2)

Moosli
Moosli

Reputation: 3285

You would need to Loop though all Fiels in this Folder and compare the File Names like this:

Dim StrFile As String

    StrFile = Dir(ThisWorkbook.Path & "\*" & ".xlsm")
    Do While Len(StrFile) > 0
        If StrFile Like "*Name*" Then
            MsgBox StrFile 'This will be your File

        End If
        StrFile = Dir
    Loop

Upvotes: 2

DaDirnbocher
DaDirnbocher

Reputation: 161

ThisWorkbook.Path Returns the path without trailing backslash, try 

Found = Dir ( Path & "\" & "*NAME*.xlsx")

Upvotes: 4

Related Questions