# symbol is not taken as a character

[wist]

Hey,

I have a bunch of files with similar names but I only need to list the ones that contain "#" character. I tried a couple of ways but no luck.

File list:
Intercompany Recs Bulgaria # 0059.xlsb
Intercompany Recs Bulgaria ∣ 0059 - Copy.xlsb
Intercompany Recs Bulgaria ☠ 0059.xlsb
Intercompany Recs Bulgaria ☢ 0059 - Copy.xlsb
Intercompany Recs Bulgaria ✲ 0059 - Copy (3).xlsb
Intercompany Recs Bulgaria ☕ 0059 - Copy (2).xlsb

Private Sub PeriodBox_Click()
Dim Recs As String

If Dir(path & "\Intercompany Recs * # *") <> "" Then 'this part of code returns only the # file as expected   
            
    Recs = Dir(path & "\")
        Do While Recs <> ""
            If Recs Like "Intercompany Recs * # *" Then FileBox.AddItem "IC Rec | " & Right(Recs, 9)
            Recs = Dir
        Loop
End Sub


[B]OR I also tried[/B]


Private Sub PeriodBox_Click()
Dim Recs, hash As String

hash = Chr(35)


If Dir(path & "\Intercompany Recs * # *") <> "" Then 'this part of code returns only the # file as expected   
            
    Recs = Dir(path & "\")
        Do While Recs <> ""
            If Recs Like "Intercompany Recs *" & hash & "*" Then FileBox.AddItem "IC Rec | " & Right(Recs, 9)
            Recs = Dir
        Loop
End Sub

it always adds the whole list and not just the one file I have ...I don't get why Dir function is working properly and the next one not

Any ideas?

Thanks

 

[Fluff]

Try

Recs = Dir(Path & "\Intercompany Recs * # *")

 

[wist]

Thanks Fluff, that works but I might need to use that string in other instances so I wonder why it's not accepted ...I think it takes the # as a wildcard for numbers but why it doesn't work with Chr(34) is a mystery

 

[Fluff]

Not sure what you are trying to do, can you please explain what is not working?

 

[Norie]

# is a wild card character.

Why not use Dir to return all the (xlsb) files in the folder and then using InStr to determine if they have a '#' in the filename?

 

[pgc01]

Hi wist

To match a Like special character enclose it in square brackets

Try:

MsgBox "abc # def" Like "* # *"
MsgBox "abc # def" Like "* [#] *"

 

[wist]

@pgc01, YESSS!! this is the thing I was hoping for ..thank you sir
@Fluff, I meant that I would reference the file name with different variables more times in the script ..thanks
@Norie, ..also could work ..thanks as well

 

[pgc01]

You're welcome. Thanks for the feedback.