using VBA to get data from text file

[NixLouise]

Hello, I was wondering if someone could help me point in the right direction for using VBA to take data out of notepad file and paste the data into as excel file.
I also need to extract information from the title of each of the text files and put that into the excel file.

The name of each notepad file is set up as ‘USER NAME_DATE’

I need the date to go into the first column and then the user name in the second in the excel spreadsheet.
Then the data from within the note pad which is currently set out like this:
26,3,30,0,0,0,0,0,0,0,0,0,0,0,0,0,CTA / NA, comments.

Each number needs to go into a certain colour e.g first number out of the text file needs to go into first blank row in A.

I have only just started using VBA and this is way above my current abilities. I can open notepad and copy data directly out it, but extracting information out of the file name and making the data go into certain columns I have no idea how to do. Also to make it run through all the text files in the folder before finishing.

Any help at all would be greatly appreciated.
Thank you

 

[MARK858]

You can try the code below which will get you started (it doesn't loop through the files (just make sure the activesheet is blank))

Sub GetCsv()
    Dim fStr As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancel Selected"
            Exit Sub
        End If
        fStr = .SelectedItems(1)
    End With

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & fStr, Destination:=Range("$A$1"))
        .Name = "DATA"
        .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 = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    End With
End Sub

To place it on the 2nd row change

 "TEXT;" & fStr, Destination:=Range("$A$1"))

to

"TEXT;" & fStr, Destination:=Range("$A$[B][COLOR="#FF0000"]2[/COLOR][/B]"))

 

[NixLouise]

Thank you very much for your help. Do you know of anyway I can get the information from the file name (of the notepad) and import that in to the excel workbook?

 

[MARK858]

Try the amended code below

Sub GetCsv()
    Dim fStr As String, fPath As String, MyName As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancel Selected"
            Exit Sub
        End If
        fStr = .SelectedItems(1)
    End With
    fPath = Right(fStr, Len(fStr) - InStrRev(fStr, "\"))
    MyName = Left(fPath, Len(fPath) - 4)
    Range("A1").Value = MyName
    With ActiveSheet.QueryTables.Add(Connection:= _
                                     "TEXT;" & fStr, Destination:=Range("$A$2"))
        .Name = "DATA"
        .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 = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    End With
End Sub