Insert new file name in Excel VBA

Question

Below is a macro I recorded for a project that I am working on. There is a new text file that comes weekly (in this example the file name is HMO04102015.txt) and I am wondering whether there is a way (maybe an additional macro that I can put into the code) that I can pull the new weekly file name and replace it automatically (maybe like a "pull most recent time stamp" function). The files come on the same day of the week so I know what day they are coming (if that helps at all).

Sub textimport()
'
' textimport Macro
'
    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;C:\Users\Employee\Desktop\Test Data\HMO04102015.txt", _
            Destination:=Range("$A$1"))
        .Name = "HMO04102015"
        .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(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(11, 29, 9, 7, 2, 3, 167, 51, 39, 18)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").EntireColumn.AutoFit
    Selection.ColumnWidth = 27
    Range("B2").Select
End Sub

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

Yes you can use Application.GetOpenFilename to choose the file. See this code

If you want the file with the recent timestamp, then that can be easily ADDED as well. You will have to loop through files in a folder using DIR and checking for the latest Time Stamp.

Sub textimport()
    Dim Ret

    Ret = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    If Ret <> False Then
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Ret, _
        Destination:=Range("$A$1"))
            '
            '~~> Rest of the code
            '
        End With
    End If
End Sub

edited May 23 '17 at 12:21

Community

1
1

answered May 17 '15 at 06:04

Siddharth Rout

147,039
17
206
250

Thank you for your input. Yes, I would probably want to grab the latest file, seeing that multiple files are housed in one folder (e.g. TestFile04102015, TestFile04172015,TestFile04242015). You mentioned that to pick the most recent file in I would need to incorporate the DIR fuction. How would I do that (using the example file name TestFile04242015)? Thanks for all your help! – John_Hector May 17 '15 at 22:38
Will the filename always have the latest time stamp? – Siddharth Rout May 18 '15 at 06:42
Hi Siddharth, yes the file will have the same name, although each files have a time stamp (mm-dd-yyy) so, for example, one file may be named TestFile04102015 and the next file will be name TestFile04172015. All files are exactly 7 days apart. – John_Hector May 18 '15 at 11:16
So why not loop through the files and extract 8 number from right of the filename and check which is the latest? I have already given you a link to loop through the file? – Siddharth Rout May 18 '15 at 11:42

Insert new file name in Excel VBA

1 Answers1