Retrieving data from a .txt file

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
You can import the data. There is a "from Txt" option under "get external data" under the Data tab.
 
Upvote 0
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;

Code:
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
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0
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.

 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top