Manually paste text into a workbook? No way!

Recently, I had a bunch of cool PowerPoint 2010 templates that I wanted to publish. A colleague was going to take each template and a short paragraph describing the template and create the downloadable files. So I saved the PowerPoint templates (.potx) to a network file share and then created a matching text file (.txt) for each template with a description in the same folder.

Shortly afterward, my colleague asked me if I had any descriptions for the templates. “Yes,” I say, “they’re included in that shared folder with the templates. I saved each description as a text file.”

Nonplussed, my colleague asks, “Um, could you put all of the descriptions into a single Excel workbook?”


So here’s my problem. I needed to turn this:

 Image of shared folder

… into this:

 Image of worksheet with template descriptions

Altogether, the folder had approximately 40 text (.txt) files that I needed to compile into the workbook, where the content would be listed out row by row on a single worksheet. From left to right, each row needed to include my name, the file name of the PowerPoint template, the template’s title (the file name cleaned up a bit), and the 255 character description.

Certainly, I could have typed the filename into the second column, wrote a formula in the third column to translate the filename into a title, and then copied and pasted from the text file into the workbook. Or, if had been so inclined, I could have just used the From Text command in the Get External Data group on the Data tab.

Spend that kind of time creating a workbook? Pshaw!

Instead, I wrote some Visual Basic code to pull the data that I need from the folder with the PowerPoint and text files and then enter it into the workbook.

To get the text from the .txt files into the Excel workbook, the code uses the Directory.GetFiles method and a search pattern to open only the text files in the specified folder. It enters the name of the text file to the next blank row in column B of the active sheet. Then the code opens each text file with a StreamReader object and appends each line of text from the file to a string variable. Once each line of text in the file has been written to the string, the string is added to column D in the same row. Then the row counter is incremented to move to the next row and the process repeats until it has transcribed each .txt. file in the specified folder.

Here’s the code that I used. It is a workbook-level project using Visual Studio 2010, Visual Basic, .NET 4, and Excel 2010.

Imports Microsoft.VisualBasic.FileIO

Imports System.IO

Imports System

Public Class ThisWorkbook

    Private Sub ThisWorkbook_Open() Handles Me.Open

        ‘ Create an array where each item is a text file in the folder

        Dim MyFiles As String() = Directory.GetFiles(“C:My DocumentsImportText_TEST”, “*.txt”)

        Dim MyFile As String

        Dim MyWorkbook As Excel.Workbook = Me.Application.ActiveWorkbook

        Dim MySheet As Excel.Worksheet = Me.Application.ActiveSheet

        ‘ Set up header rows

        With MySheet

            .Range(“A5”).Value = “ contact”

            .Range(“B5”).Value = “File Name”

            .Range(“C5”).Value = “Template Title”

            .Range(“D5”).Value = “Description”

        End With

        ‘ Create a counter for workbook rows

        Dim numline As Integer = 6

        For Each MyFile In MyFiles

            ‘ Create a StreamReader object

            Dim MyReader As StreamReader = New StreamReader(MyFile)

            Dim currLine As String

            Dim currCellValue As String = “”

            ‘ MyFileName string includes directory

            ‘ The file name of text file begins at character 32

            ‘ Enter file name and template name into spreadsheet

            With MySheet

                .Range(“A” & numline).Value = “Eric Schmidt”

                .Range(“B” & numline).Value = _

                MyFile.Substring(32).Replace(“.txt”, “.potx”)

                .Range(“C” & numline).Value = _

                MyFile.Substring(32).Replace(“_”, ” “).Replace(“.txt”, “”)

            End With


                ‘ Read each line that contains text and add to currCellValue string

                currLine = MyReader.ReadLine()

                currCellValue = currCellValue + ” ” + currLine

            Loop Until currLine Is Nothing

            ‘ Close the text file


            ‘ Enter currCellValue into the spreadsheet

            With MySheet.Range(“D” & numline)

                .Value = currCellValue

                .WrapText = True

            End With

            numline = numline + 1


        ‘ Resize the rows and columns in the workbook

        With MySheet

            .Columns(“D”).ColumnWidth = 75

            .Range(.Cells(1, 1), _

                   .Cells(numline, 4)).Rows.AutoFit()


        End With

    End Sub

End Class

To write this code, I consulted the following articles:

–Eric Schmidt

 Eric Schmidt is a programming writer for Visio.