CODEWITHSUNDEEP
excel
Pramod
Pramod

Reputation: 1

How to give dynamic name to file which is used as source file in macro?

Daily I run a text file using an Excel macro. However, I want to rename this file every day as "aa". unless I give this name properly macro will not work.

I have mentioned here with the original file name. Note, only this file date is changed day to day.

Filename: 20017122018.ASCII.TXT

The given name is changed day to day as mentioned below.

20017122018.ASCII.TXT

20118122018.ASCII.TXT 

20219122018.ASCII.TXT 

20320122018.ASCII.TXT

Update

This is my recorded macro code. How can I correct this? I want to replace aa.txt by standard file name.

With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\aa.txt", Destination _
    :=Range("$A$1"))
    .Name = "aa"
    .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 = xlFixedWidth
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(9, 2, 9, 1, 2, 9, 1, 1, 1, 1, 1, 1, 1, 1, 1, 9, 1, 9, 1, 9, 1, _
    9)
    .TextFileFixedColumnWidths = Array(4, 16, 7, 6, 16, 8, 4, 12, 3, 12, 3, 25, 13, 3, 4, 13, 1 _
    , 1, 6, 7, 3)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
Cells.Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$O$828").AutoFilter Field:=6, Criteria1:="144"
Range("G54").Select

Upvotes: 0

Views: 354

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

This code will display only the *.ASCII.txt files in the selected folder.
Select the file you want and it will open it.

This tells it to show the files that are in the same folder as the Excel file: 

vFile = GetFile(ThisWorkbook.Path)

This tells it to show the files that are in the Default file location: 

vFile = GetFile()

You then reference the text file using wrkBk.

This is the whole procedure: 

Sub Test()

    Dim vFile As Variant
    Dim wrkBk As Workbook

    vFile = GetFile()

    If vFile <> "" Then
        Set wrkBk = Workbooks.Open(vFile)
    End If

End Sub

Function GetFile(Optional startFolder As Variant = -1) As Variant
    Dim fle As FileDialog
    Dim vItem As Variant
    Set fle = Application.FileDialog(msoFileDialogFilePicker)
    With fle
        .Title = "Select a File"
        .AllowMultiSelect = False
        .Filters.Add "ASCII.TXT", "*.ASCII.txt", 1
        If startFolder = -1 Then
            .InitialFileName = Application.DefaultFilePath
        Else
            If Right(startFolder, 1) <> "\" Then
                .InitialFileName = startFolder & "\"
            Else
                .InitialFileName = startFolder
            End If
        End If
        If .Show <> -1 Then GoTo NextCode
        vItem = .SelectedItems(1)
    End With
NextCode:
    GetFile = vItem
    Set fle = Nothing
End Function

Upvotes: 1

Related Questions