Working backward: rows to text files

You may remember this graphic from my previous post:

It was totally staged. A fabrication.

Well, the PowerPoint templates are the original files - so it wasn't completely faked. But I added all of the text files back into the folder in order to create the image.

As I worked on the previous post, I realized that I should include an image that visually demonstrates what I needed to accomplish. Yet, by the time I wrote the post, I had already handed off those templates and all the (now unnecessary) text files had been deleted.

So how did I create this image, you may ask? Simple: I wrote code to reverse the process from the earlier post! The program below takes rows in an Excel workbook and generates text files from the data in the rows.

The programming is quite simple, once you understand how to create and use StreamWriter objects. When the workbook is opened, for each row of data, the code gets the filename from column B in the first spreadsheet in the workbook, replaces the PowerPoint template file extension (.potx) with the text file (.txt), opens a StreamWriter that creates the new text file, transfers the value from column C to the text file, and closes the text file.

Here's the code that I used. It is a workbook-level project using Visual Studio 2010, Visual Basic, .NET 4, and Excel 2010. (If you want to know what the workbook looks like, refer to my previous post.)

Imports System

Imports System.IO

Public Class ThisWorkbook

    Private Sub ThisWorkbook_Open() Handles Me.Open

        Dim MyWorkbook As Excel.Workbook = _

            Me.Application.ActiveWorkbook

        Dim MySheet As Excel.Worksheet = MyWorkbook.Worksheets(1)

        Dim numRow As Integer = 6

        Dim MyTextFileName As String

        Dim currCell As Excel.Range = MySheet.Range("B" & numRow)

        While Not currCell.Value = ""

            MyTextFileName = currCell.Value

            MyTextFileName = MyTextFileName.Replace(".potx", ".txt")

            Using MyStreamWriter As New StreamWriter("C:\My Documents\ImportText_TEST\" & MyTextFileName)

                MyStreamWriter.Write(MySheet.Range("D" & numRow).Value.ToString())

            End Using

            numRow = numRow + 1

            currCell = MySheet.Range("B" & numRow)

        End While

        Finalize()

        ' This program used the following articles:

        ' http://msdn.microsoft.com/en-us/library/36b93480.aspx

        ' http://msdn.microsoft.com/en-us/library/6ka1wd3w.aspx

    End Sub

End Class

Here's another version of the project, written in VBA:

Sub ExportToText()

    Dim MyWorkbook As Excel.Workbook

    Dim MyWorksheet As Excel.Worksheet

    Dim currCell As Excel.Range

    Dim MyTextFileName As String

    Dim numRow As Integer

    numRow = 6

    Set MyWorkbook = Application.ActiveWorkbook

    Set MySheet = MyWorkbook.Sheets(1)

    Set currCell = MySheet.Range("B" & numRow)

    Do Until currCell.Offset(numRow, 0) = vbNullString

        MyTextFileName = currCell.Offset(numRow, 0).Value

        MyTextFileName = Replace(MyTextFileName, ".potx", ".txt")

        Call SetFileAndText(MyTextFileName, currCell.Offset(numRow, 2).Text)

        numRow = numRow + 1

    Loop

    ' This program used the following article:

    ' http://msdn.microsoft.com/en-us/library/dd439413(office.12).aspx

End Sub

Sub SetFileAndText(fileName, fileText)    

    ' Add Microsoft Scripting Library to Project references.

    Dim fso As New FileSystemObject

    Dim stream As TextStream

    Dim myText As String

    Set stream = fso.CreateTextFile("C:\My Documents\" & fileName)

    stream.Write fileText

    stream.Close

End Sub

To write this code, I consulted the following article:

• How to: Write text to a file
• Writing to a Text File from within a Macro in the 2007 Office System

--Eric Schmidt

 Eric Schmidt is a programming writer for Visio.