# 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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,092
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
73,092
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
76,343
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,887
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

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
Top