# symbol is not taken as a character

wist

Board Regular
Joined
Nov 8, 2012
Messages
79
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.

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

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
Recs = Dir(Path & "\Intercompany Recs * # *")
 

wist

Board Regular
Joined
Nov 8, 2012
Messages
79
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Not sure what you are trying to do, can you please explain what is not working?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

# 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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,838
Hi wist

To match a Like special character enclose it in square brackets

Try:

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

wist

Board Regular
Joined
Nov 8, 2012
Messages
79
@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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,416
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top