VBA Code that pulls a file that has a dynamic ending

[tacole475]

My file path (\\cat04\PAT\PROD\CAT\STUCT\In\CEE\) does not change, but the file name will change daily.

The tab labeled Main (a8 in this case has 11-17-2017) will have the date that I would like to pull.

The current code written below will try to find \\cat04\PAT\PROD\CAT\STUCT\In\CEE\CAL_BALSUM\20171117_D_3.3_20171117_.txt

But what I need the code to pull is \\[URL="file://cat04/PAT/PROD/CAT/STUCT/In/CEE/"]cat04\PAT\PROD\CAT\STUCT\In\CEE\CAL_BALSUM\20171117_D_3.3_20171117_######.txt[/URL] The # signs indicates a 6 digit random number that will change daily.

Private Const strPath As String = "\\cat04\PAT\PROD\CAT\STUCT\In\CEE\"
Private Const lngHeader As Long = 2

Sub Balance_Summary()
Dim strFileName As String

With wksData

.Range(.Cells(lngHeader, 1), .Cells(.Rows.Count, .Columns.Count)).Clear

strFileName = "CAL_BALSUM_" & Format(ThisWorkbook.Worksheets("main").Range("B8"), "yyyymmdd") & "_D_3.3_" & Format(ThisWorkbook.Worksheets("main").Range("B8"), "yyyymmdd") & "_"


With .QueryTables.Add _
(Connection:="TEXT;" & strPath & strFileName & ".txt", _
Destination:=.Cells(lngHeader, 1))
.Name = strFileName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End With

End Sub

 

[AFPathfinder]

Do you need to specify the 6 digits at runtime or open a workbook without regards to the six digits, as long as everything else matches?

I believe you can use "*" in the string to signify any number of characters as a wildcard. If you need to specify the exact number of characters, I think "??????" is the necessary syntax in the string (one ? for each character). Using these wildcards allows a search regardless of the characters replaced with the wildcard(s).

 

[Fennek]

Hello,

is it possible to use the timestamp?

regards

 

[AFPathfinder]

I suppose you can, but how do you want to use it?

 

[Norie]

Is CAL_BALSUM part of the filename or another folder as it appears here?

\\cat04\PAT\PROD\CAT\STUCT\In\CEE\CAL_BALSUM\20171117_D_3.3_20171117_.txt

 

[tacole475]

The file name is CAL_BALSUM\20171117_D_3.3_20171117_(with a random 6 digit number at the end).txt

 

[tacole475]

The date on the main tab in cell A8 will be the driving what file is pulled. The folder has daily files so using a timestamp could work if I could pick the different timestamps an not the most current file.

 

[tacole475]

\\cat04\PAT\PROD\CAT\STUCT\In\CEE\ is the path this will not change
CAL_BALSUM\20171117_D_3.3_20171117_######.txt is the file name

I tried:

strFileName = "CAL_BALSUM_" & Format(ThisWorkbook.Worksheets("main").Range("B8"), "yyyymmdd") & "_D_3.3_" & Format(ThisWorkbook.Worksheets("main").Range("B8"), "yyyymmdd") & "_", &*"

but it just pulls back strFileName = CAL_BALSUM\20171117_D_3.3_20171117_*.txt

 

[AFPathfinder]

At the end of "CAL_BALSUM" there is a difference in your code vs the stated filename. The \ character typically signifies the end of a folder name, but strFileName is being defined with an underscore. Is the filename "CAL_BALSUM_20171117_D_3.3_20171117_######.txt" or "20171117_D_3.3_20171117_######.txt" instead of "CAL_BALSUM\20171117_D_3.3_20171117_######.txt"?

 

[tacole475]

sorry that was typed incorrectly. you are correct the file name is CAL_BALSUM_20171117_D_3.3_20171117_######.txt