Import dynamic filname

I'm importing some CSVs that have names that change every month (oct13, nov13, dec13, etc.)

I would like to create a variable that contains a wildcard, but VBA doesn't seem to like what I've written.

If I write the whole file name in, it works correctly, but if I add in an *, it doesn't

• This variable works --> strFileName = "Oct13_Future" & ".csv"
• This is what I'm trying to accomplish --> strFileName = "*" & "_Future" & ".csv"

Any assistance would be greatly appreciated.

Thanks,
7

not sure how to work that into my script, as I think I need the full filename to build my connection.

here's what I've got so far, and it works, but since the filenames will change each month, I need the strConnection variable to be dynamic.

again; any help will be greatly appreciated

Sub ProcessDTA()

    Dim wbPath, strFutureName, strHistoryName, strConnection, strTempFile As String

    wbPath = Application.ActiveWorkbook.Path
    strFutureName = "ameroct13_Future" & ".csv"
    strHistoryName = "ameroct13_History" & ".csv"

    Dim row As Integer
    row = 2
    On Error Resume Next
    row = Range("A1").End(xlDown).row + 1

    strConnection = "TEXT;" & wbPath & "\Data\" & strFutureName
    With ActiveSheet.QueryTables.Add(Connection:= _
         strConnection, Destination:=Range("$A$" & row))
        .Name = "temp"
        .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 = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    Workbooks.Open (wbPath & "\Data\" & strHistoryName)
    wbPath = Application.ActiveWorkbook.Path
        
    Columns("AG:AK").Select
    Selection.Cut
    Columns("Y:Y").Select
    Selection.Insert Shift:=xlToRight
    Columns("Y:AX").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWorkbook.SaveAs Filename:=wbPath & "\tmp_" & strHistoryName, FileFormat:=xlCSV, CreateBackup:=False
        
    Application.DisplayAlerts = False
        ActiveWorkbook.Close
    Application.DisplayAlerts = True

    row = Range("A1").End(xlDown).row + 1
    strConnection = "TEXT;" & wbPath & "\tmp_" & strHistoryName
    With ActiveSheet.QueryTables.Add(Connection:= _
         strConnection, Destination:=Range("$A$" & row))
        .Name = "temp"
        .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 = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    strTempFile = wbPath & "\tmp_" & strHistoryName
    Kill strTempFile

    MsgBox "Data Transformation Complete", vbInformation, "Data Transformation"

End Sub

ok, it's a little rough around the edges, but I got it figured out. Thanks for your help.

Sub ProcessDTA()

On Error GoTo ExitPoint

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With

    Dim wbPath, MyDocuments, strToday, strFileName, strFutureName, strHistoryName, strConnection, strTempFile As String

    wbPath = Application.ActiveWorkbook.Path
    'MyDocuments = Environ$("USERPROFILE") & "\My Documents"
    'strToday = Format(Date, "mmddyy")
    strFileName = Dir(wbPath & "\Data\*.csv")

    Do While Len(strFileName) > 0
        If Right(strFileName, 11) = "_Future.csv" Then
            strFutureName = strFileName
            
            Sheets("Data").Select
            
            Dim row As Integer
            row = 2
            On Error Resume Next
            row = Range("A1").End(xlDown).row + 1

            strConnection = "TEXT;" & wbPath & "\Data\" & strFutureName
            With ActiveSheet.QueryTables.Add(Connection:= _
                strConnection, Destination:=Range("$A$" & row))
                .Name = "temp"
                .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 = 2
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With
        
        ElseIf Right(strFileName, 12) = "_History.csv" Then
            strHistoryName = strFileName

            Workbooks.Open (wbPath & "\Data\" & strHistoryName)
            wbPath = Application.ActiveWorkbook.Path
        
                Columns("AG:AK").Select
                Selection.Cut
                Columns("Y:Y").Select
                Selection.Insert Shift:=xlToRight
                Columns("Y:AX").Select
                Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                ActiveWorkbook.SaveAs Filename:=wbPath & "\tmp_" & strHistoryName, FileFormat:=xlCSV, CreateBackup:=False
        
                Application.DisplayAlerts = False
                    ActiveWorkbook.Close
                Application.DisplayAlerts = True

                row = Range("A1").End(xlDown).row + 1

                strConnection = "TEXT;" & wbPath & "\tmp_" & strHistoryName
                With ActiveSheet.QueryTables.Add(Connection:= _
                    strConnection, Destination:=Range("$A$" & row))
                    .Name = "temp"
                    .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 = 2
                    .TextFileParseType = xlDelimited
                    .TextFileTextQualifier = xlTextQualifierDoubleQuote
                    .TextFileConsecutiveDelimiter = False
                    .TextFileTabDelimiter = False
                    .TextFileSemicolonDelimiter = False
                    .TextFileCommaDelimiter = True
                    .TextFileSpaceDelimiter = False
                    .TextFileColumnDataTypes = Array(1)
                    .TextFileTrailingMinusNumbers = True
                    .Refresh BackgroundQuery:=False
                End With

                strTempFile = wbPath & "\tmp_" & strHistoryName
                Kill strTempFile
        End If
        
        strFileName = Dir
    Loop

    Sheets("Home").Select

    MsgBox "Data Transformation Complete", vbInformation, "Data Transformation"

ExitPoint:
With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub