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

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try
Code:
Recs = Dir(Path & "\Intercompany Recs * # *")
 
Upvote 0
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:
Upvote 0
Not sure what you are trying to do, can you please explain what is not working?
 
Upvote 0
# 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?
 
Upvote 0
Hi wist

To match a Like special character enclose it in square brackets

Try:

Code:
MsgBox "abc # def" Like "* # *"
MsgBox "abc # def" Like "* [#] *"
 
Upvote 0
@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:
Upvote 0

Forum statistics

Threads
1,214,911
Messages
6,122,199
Members
449,072
Latest member
DW Draft

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top