Referencing a partial file name

[mbkinzer]

Hello,

I am trying to hyperlink a file. The first part of the filename is constant but the end has a variable date. Is there a way to look at the constant portion only when referencing?
For example the file's name is: "EarningsDiscrepancy_0069_2021_20220429_093447_5649"
I would like for it to recognize just "EarningsDiscrepancy" and hyperlink that file.
The file path "strEDFolder" is fine.

My current attempt that is not working:

Dim strAgencyFolder As String
strAgencyFolder = ActiveWorkbook.Path

Dim strEDFolder As String
strEDFolder = strAgencyFolder & "\Support Docs\"

Dim Strfile
Strfile = Dir(strEDFolder & "EarningsDiscrepancy*" & "\*")

Range("C18").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:=strEDFolder & "EarningsDiscrepancy*" & "\*", _
TextToDisplay:="Earnings Discrepancy"

 

[Micron]

If underscore is constant and you want what comes before it, use combination of Left and Instr functions.
Left("EarningsDiscrepancy_0069_2021_20220429_093447_5649",Instr("EarningsDiscrepancy_0069_2021_20220429_093447_5649","_")-1)
will return EarningsDiscrepancy
You would substitute your variable or cell address for that long string.

 

[mbkinzer]

If underscore is constant and you want what comes before it, use combination of Left and Instr functions.
Left("EarningsDiscrepancy_0069_2021_20220429_093447_5649",Instr("EarningsDiscrepancy_0069_2021_20220429_093447_5649","_")-1)
will return EarningsDiscrepancy
You would substitute your variable or cell address for that long string.

Hi, sorry what do you mean I would substitute my variable for the long string?

 

[Micron]

I wasn't sure you had one, or even a cell reference because not all the code is there & can't tell what's going into the variables you do have.
If you don't have either, declaring a string variable and assigning EarningsDiscrepancy_0069_2021_20220429_093447_5649 to it would shorten that long expression. If that's not a concern, you could just leave it as I posted.

 

[mbkinzer]

I wasn't sure you had one, or even a cell reference because not all the code is there & can't tell what's going into the variables you do have.
If you don't have either, declaring a string variable and assigning EarningsDiscrepancy_0069_2021_20220429_093447_5649 to it would shorten that long expression. If that's not a concern, you could just leave it as I posted.

Ok. The file name of "EarningsDiscrepancy_0069_2021_20220429_093447_5649" changes. For example could be EarningsDiscrepancy_0023_2021_20220429_093447_111 instead. So when am referencing the filepath> filename, how do I make sure it selects any file that is labeled "EarningsDiscrepancy_" regardless of what comes after?

 

[Micron]

Is there a way to look at the constant portion only when referencing?

The expression I posted will do that. If you want the _ then delete the -1. Then you'd use wildcards in your use of Dir function as you already are.
If your problem is related to the hyperlinking part, then I'm afraid I don't know what the issue is or how to fix it. If by "any file" you mean each one in a folder that starts with EarningsDiscrepancy then you need to code so as to loop over files in a folder - a very commonly asked question, so there's lot's of code examples for that.