We help IT Professionals succeed at work.

Macro to open a .txt file and save as an .xlsx file

I need a macro that will go to a spceified file location in excel (located in cell a1) that will search all subfolders within that path, look for a *_*.txt file  and it will open the text file, save it as an .xlsx file, remove the underscore from the name and tack on a date (located in cell a2 ('11-02-2011).  Is that possible?  Thanks!!!
Comment
Watch Question

Commented:
change the file locations to the txt file and where you want the save file to be located.
Sub ImportSave()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\file location\test.txt", _
        Destination:=Range("$A$1"))
        .Name = "Rescued document"
        .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 = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    ActiveWorkbook.SaveAs Filename:= _
    "C:\file location\test1.xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.