Pulling information from text files into Excel

It’s been a while since I’ve done one of these types of problems, but I wanted to look at some data that resided in text files (.txt) and I had never tried to pull in data from text files into Excel. The itch I wanted to scratch was Zoom call attendance: was attendance declining and how did the range of attendance (those who attended once all the way to those who attended every call) looked in a graph?

As far as I can tell, there are two ways of bringing in data from text files and the success of either option will most likely depend on the way the data is structured in the text file.

First, before I forget, I found the answer here.

Some background on my data. I have a folder where I stored the text files whose ending appendage was .txt. I wanted VBA to go to this folder and pull up each text file and copy the data from each text file into Excel. The counting of people and the number of times they attended the call was done through a dictionary method which I won’t describe here. There are plenty of other sites that discuss the use of dictionary, and I did not do anything unusual or unique.

Method 1: allow the file to open up in Excel application

This method actually worked best for me but in the StackOverFlow site, this method did not work for the person asking for assistance. This code actually opened up each text file residing in a specific folder (HostFolder is a variable to hold the directory path) as an Excel file.

  Dim File
  For Each File In Folder.Files

        HostFolder = Range("F3") 'Directory path to folder where the text files are located

        filename = HostFolder & "\" & File.Name
        Set mytextfile = Workbooks.Open(filename)
        mytextfile.Sheets(1).Cells.Copy    
        ThisWorkbook.Sheets("Test".Range("A1")
        
        Application.DisplayAlerts = False 'Stop Excel from interrupting
        mytextfile.Close
        Application.DisplayAlerts = True

  Next File

Method 2: using FileSystemObject and runtime scripting

Every once in a while, I run into the FileSystemObject method which seems to open up some kind of guts in the programming. When I generally have to do one of these, I generally have to make sure I add in the runtime scripting that can be found in the Tools – References menu.

In VBE window, click Tools » References » scroll down, find, and tick Microsoft Scripting Runtime


        Dim oFSO As New FileSystemObject
        Dim oFS As TextStream
        
        Dim fileName As String
        ' make sure to update your path or
        ' pass it to the sub through parameter
        'fileName = "C:\Users\fooboo\Desktop\text.txt"
        fileName = HostFolder & "\" & File.Name
    
        Set oFS = oFSO.OpenTextFile(fileName)
    
        Dim content As String
        content = oFS.ReadAll
    
        With Sheets("Test").Range("A1")
            .ClearContents
            .NumberFormat = "@"
            .Value = content
        End With
    
        oFS.Close
        Set oFS = Nothing
        Set oFSO = Nothing

This method seems to dump all of the materials in the text file, which by the way is not opened in Excel, into a variable called content. Then I took the content variable and dumped the data from the content variable into a tab in Excel. One problem with that method for me, everything went into one cell A1. All of the data had to go into A1.

Cells in Excel have a limit to how much character can reside in a cell so the content being copied into Excel was truncated by the size limitation of cell A1.

Fortunately for me, method 1 worked for me, even though it didn’t work for the person in StackOverFlow.