Constantly Changing Number in File Name Issue

[Cagey93]

Hello,

I have a job at the moment to automate the generation of a lot of PDF's from various files and I have come across a problem. The files in question change every month so I have had to get creative with the formula so the cell always has the correct file names. The issue I have is this example below which as you can see is looking at a file path but all the variables have to be coded such as the year / month folder and the file name its self.

Now the file name is the part which is having the issue so an example file name is "19,05,10 SecuredFundingAgenda.docx".

The first part of the file 19,05,10 is the part that always changes. The 19,05 is easy as you can see I've got it looking at a cell which will contain this section but its the 10 that is causing a problem I have put "**" as I thought the ** would indicate 2 random numbers which I will never know what they will be and that tells Excel when its looking for that file path via VBA the numbers will vary so look for the numbers you know to be the beginning and then fill in that blank. However it is not working it simply doesn't pick up the name. where am I going wrong?

="\\companyname\department\team\Secured Funding Committee Meetings\Agenda"&TEXT(EDATE(TODAY(),-1),"YYYY")&""&$D$6&","&"**"&" SecuredFundingAgenda.docx"

Thanks in advance

 

[baitmaster]

I'm slightly unclear on exactly what you're trying to do, but it feels like there is a better way to do it

Options for selecting source files might include: looping through all files in folder using DIR; allowing user to select via a file>open dialogue box; having specific files open at start of VBA process, perhaps with "names" inside them that flag them for use

Why do you need this specific value stored in a worksheet cell? Are you trying to match the entire text string, and could you use e.g. INSTR(...) to match partial strings instead?

 

[Cagey93]

I'm slightly unclear on exactly what you're trying to do, but it feels like there is a better way to do it

Options for selecting source files might include: looping through all files in folder using DIR; allowing user to select via a file>open dialogue box; having specific files open at start of VBA process, perhaps with "names" inside them that flag them for use

Why do you need this specific value stored in a worksheet cell? Are you trying to match the entire text string, and could you use e.g. INSTR(...) to match partial strings instead?

I'm trying to make it as easy as possible for the users so they don't accidentally select the wrong file which will cause issues further down the line so I know what the constant text bits are its only the third number the 10 which will be random so I want it to look for the beginning part which is always going to be Year then Month so 19,05 in the example followed by the SecuredFundingAdenga.docx section.

I will try the INSTR to see if I can make it select the file with the string 19,05 at the start.

 

[baitmaster]

here's some ideas for VBA tests, which ignores the middle section

Option Explicit

Sub testFileNames()


Dim wb As Workbook: Set wb = ThisWorkbook    ' will need to change depending on what workbook object you want to use


Dim str1 As String: str1 = Format(Date, "yy,mm")
Dim str2 As String: str2 = "SecuredFundingAdenga.docx"


If Len(wb.Name) < Len(str2) Then
    MsgBox "file name too short"
    Exit Sub
End If


If Left(wb.Name, 5) <> str1 Then
    MsgBox "invalid date format"
    Exit Sub
End If


If Right(str2, Len(str2)) <> str2 Then
    MsgBox "invalid file text"
    Exit Sub
End If




End Sub