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]

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

The first thing I see is a comma and quotation issue towards the end of that line. The other thing is the range reference for B8 should have a .Value or .Text on the end of it. Personal taste, but I'd suggest putting the date into a variable if you are using it more than once:

    Dim oRptDate As Date
    
    oRptDate = ThisWorkbook.Worksheets("main").Range("B8").Value

    strFileName = "CAL_BALSUM_" & Format(oRptDate, "yyyymmdd") & "_D_3.3_" & Format(oRptDate, "yyyymmdd") & "_*.xlsx"

The variable ensures the same value is used each time and if you have to change the reference, you'll only have to change that one spot.

 

[tacole475]

Thanks for the feedback. I updated the code with your suggestions. The code is still not recognizing the * to pull in the remaining file name.

The excel error when I run the macro states - "Could not open \\cat04\PAT\PROD\CAT\STUCT\In\CEE\CAL_BALSUM_20171117_D_3.3_20171117_*.txt"

Below is my VBA code.
Sub Balance_Summary()
Dim strFileName As String
Dim oRptDate As Date

oRptDate = ThisWorkbook.Worksheets("main").Range("B8").Value
With wksData

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

strFileName = "CAL_BALSUM_" & Format(oRptDate, "yyyymmdd") & "_D_3.3_" & Format(oRptDate, "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]

OK, I've been completely ignoring the fact that it's a text file. Give this one a shot and see if it solves the problem. I've never tried to import a text file as a query table before, so I'm not 100% sure.

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
    Dim oRptDate As Date
    oRptDate = ThisWorkbook.Worksheets("main").Range("B8").Value
    With wksData
        .Range(.Cells(lngHeader, 1), .Cells(.Rows.Count, .Columns.Count)).Clear
    
        strFileName = Dir(strPath & "CAL_BALSUM_" & Format(oRptDate, "yyyymmdd") _
            & "_D_3.3_" & Format(oRptDate, "yyyymmdd") & "*.txt")
    
    
        With .QueryTables.Add _
            (Connection:="TEXT;" & strFileName, _
            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

 

[tacole475]

Almost got it!!! The correct file name is now pulling but the macro is now pulling the file extension twice.

Cal_BALSUM20171117_D_3.3_20171117_221015.txt.txt

I'm sure the issue is with this part of the code but I don't know how to resolve the issue. If I take out either of the .txt it errors at the BackgroundQuery:False

strFileName = Dir(strPath & "CAL_BALSUM_" & Format(oRptDate, "yyyymmdd") _
& "_D_3.3_" & Format(oRptDate, "yyyymmdd") & "*.txt")


With .QueryTables.Add _
(Connection:="TEXT;" & strPath & strFileName & ".txt", _

 

[AFPathfinder]

Oh, yeah, the .txt is listed on both lines. Take it out of the QueryTables.Add line. I think it needs to be in the strFileName line.

[COLOR=#333333]With .QueryTables.Add _[/COLOR]
[COLOR=#333333](Connection:="TEXT;" & strPath & strFileName[/COLOR][B][COLOR=#FF0000], _[/COLOR][/B]

 

[tacole475]

Thanks for your help. The code is now working. I had to keep the ""s in the code for it to work.

With .QueryTables.Add _
(Connection:="TEXT;" & strPath & strFileName "", _

 

[AFPathfinder]

Weird. As long as it works though, good deal!