Retrieving data from a .txt file

[omairhe]

Hello,

I would like to know if I have data file in a specific folder named ="Folder A" which will be residing on my desktop at all times, would I be able to retrieve data from the last created file type = ".txt" from it. Can I do it via VBA scripting of some sort?

The data in the .txt file may look something like this...

Date: 20 August 2018
40XT50KIT
Alpha One 50
Bravo 40
Charlie 30
Delta Two 100
40BIT30KIT
Charlie 40
Delta Two 30


Thank u.

 

[BadgerRoad]

You can import the data. There is a "from Txt" option under "get external data" under the Data tab.

 

[Haluk]

Hello,

I would like to know if I have data file in a specific folder named ="Folder A" which will be residing on my desktop at all times, would I be able to retrieve data from the last created file type = ".txt" from it.

See this, if it helps;

Sub Test()
    Dim objShell As Object
    Dim MyDesktop As String, MyFolder As String, MyFile As String
    Dim MaxDateTime As Date, MyDateTime As Date
    Dim InputData As String
    
    ActiveSheet.Cells.Clear
    
    Set objShell = CreateObject("WScript.Shell")
    MyDesktop = objShell.SpecialFolders("Desktop")
    
    MyFolder = "[COLOR=#ff0000][B]Folder A[/B][/COLOR]"
    strFolder = MyDesktop & Application.PathSeparator & MyFolder
    
    If Dir(strFolder, vbDirectory) = "" Then
        MsgBox "The folder;" & vbCrLf & vbCrLf & strFolder _
        & vbCrLf & vbCrLf & "is not found. Macro will be terminated."
        Exit Sub
    End If
    
    strFolder = strFolder & Application.PathSeparator
    MyFile = Dir(strFolder & "*.txt")
    MaxDateTime = 0
    
    Do While Len(MyFile) > 0
        MyDateTime = FileDateTime(strFolder & MyFile)
        If MyDateTime > MaxDateTime Then
            MaxDateTime = MyDateTime
            strfile = strFolder & MyFile
        End If
        MyFile = Dir
    Loop
    
    Open strfile For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    i = 1
    Do While Not EOF(1)
        Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , InputData
        Cells(i, 1) = InputData
        i = i + 1
    Loop
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub

 

[Haluk]

The above code, gets data from the last saved *.txt file from "Folder A"

The below code, gets data from the last created *.txt file from "Folder A"

So, you can use the one which suits your needs.

Sub Test2()
    'Gets data from the last created text file from a specific folder
    'Haluk 12/02/2018
    
    Dim objShell As Object
    Dim MyDesktop As String, MyFolder As String, MyFile As String
    Dim strFolder As String, LastFile As String, FSO As Object, strFile As Object
    Dim MaxDateTime As Date, MyDateTime As Date
    Dim InputData As String
    ActiveSheet.Cells.Clear
    
    Set objShell = CreateObject("WScript.Shell")
    MyDesktop = objShell.SpecialFolders("Desktop")
    
    MyFolder = "[COLOR=#ff0000][B]Folder A[/B][/COLOR]"
    strFolder = MyDesktop & Application.PathSeparator & MyFolder
    
    If Dir(strFolder, vbDirectory) = Empty Then
        MsgBox "The folder;" & vbCrLf & vbCrLf & strFolder _
        & vbCrLf & vbCrLf & "is not found. Macro will be terminated."
        Exit Sub
    End If
    
    strFolder = strFolder & Application.PathSeparator
    MyFile = Dir(strFolder & "*.txt")
    MaxDateTime = 0
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Do While Len(MyFile) > 0
        Set strFile = FSO.GetFile(strFolder & MyFile)
        MyDateTime = strFile.DateCreated
        If MyDateTime > MaxDateTime Then
            MaxDateTime = MyDateTime
            LastFile = strFolder & MyFile
        End If
        MyFile = Dir
    Loop
    
    Open LastFile For Input As #1
    i = 1
    Do While Not EOF(1)
        Line Input #1, InputData
        Cells(i, 1) = InputData
        i = i + 1
    Loop
    Close #1
    
    Set FSO = Nothing
End Sub

 

[omairhe]

Hello Haluk,

The Last Created .txt is more preferable to me at the moment.
Could I ask of you another favor though?

In Notepad the .txt file is in multiple rows meaning something like I posted in the original post..

Date: 20 August 2018
40XT50KIT
Alpha One 50
Bravo 40
Charlie 30
Delta Two 100
40BIT30KIT
Charlie 40
Delta Two 30

and now after importing I get all the text in a single cell ref A1 = Date: 20 August 2018 40XT50KIT Alpha One 50 Bravo 40 Charlie 30 Delta Two 100 40BIT30KIT Charlie 40 Delta Two 30

I know this could be dealt with later on..but if it is not much trouble could you please incorporate this with your "last created" code.

Thank you.

The above code, gets data from the last saved *.txt file from "Folder A"

The below code, gets data from the last created *.txt file from "Folder A"

So, you can use the one which suits your needs.

Sub Test2()
    'Gets data from the last created text file from a specific folder
    'Haluk 12/02/2018
    
    Dim objShell As Object
    Dim MyDesktop As String, MyFolder As String, MyFile As String
    Dim strFolder As String, LastFile As String, FSO As Object, strFile As Object
    Dim MaxDateTime As Date, MyDateTime As Date
    Dim InputData As String
    ActiveSheet.Cells.Clear
    
    Set objShell = CreateObject("WScript.Shell")
    MyDesktop = objShell.SpecialFolders("Desktop")
    
    MyFolder = "[COLOR=#ff0000][B]Folder A[/B][/COLOR]"
    strFolder = MyDesktop & Application.PathSeparator & MyFolder
    
    If Dir(strFolder, vbDirectory) = Empty Then
        MsgBox "The folder;" & vbCrLf & vbCrLf & strFolder _
        & vbCrLf & vbCrLf & "is not found. Macro will be terminated."
        Exit Sub
    End If
    
    strFolder = strFolder & Application.PathSeparator
    MyFile = Dir(strFolder & "*.txt")
    MaxDateTime = 0
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Do While Len(MyFile) > 0
        Set strFile = FSO.GetFile(strFolder & MyFile)
        MyDateTime = strFile.DateCreated
        If MyDateTime > MaxDateTime Then
            MaxDateTime = MyDateTime
            LastFile = strFolder & MyFile
        End If
        MyFile = Dir
    Loop
    
    Open LastFile For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    i = 1
    Do While Not EOF(1)
        Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , InputData
        Cells(i, 1) = InputData
        i = i + 1
    Loop
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
    Set FSO = Nothing
End Sub

 

[Haluk]

The code works as the way you want, that is; it gets the data from the text file line by line and inserts into the sheet same way, as shown on the below image.

 

[Haluk]

omairhe;

Did you fix the problem ? If you wish, I can upload the file on a server.

 

[omairhe]

Oh my... I double checked my .txt file and it seems that I was confused about my own data.. And the data which I tested your code was a single row data.

Your code worked flawlessly with multiple rows.

Thanks again

omairhe;

Did you fix the problem ? If you wish, I can upload the file on a server.

 

[Haluk]

You are welcome