Working backwards: lines from a text file

You may remember this chart from my previous post:

Mass totally staged. A fabrication.

Well, PowerPoint templates include the original files so that it was not completely false. But all text added back into the folder in order to create the image.

As I worked on the previous post, I realized that I have to include an image which shows visually what I needed to do. Yet, by the time I wrote the post, I had already handed out those models and all text files (now useless) were deleted.

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

The programming is pretty simple, once you understand how to create and use StreamWriter objects. When you open the workbook, for each row of data, the code gets the name of the file from column b in the first worksheet of the workbook, which replaces the file extension of the PowerPoint template (.potx) with the text file (.txt)Opens a StreamWriter object that creates a new text file, transfer the value from column c text file and close the text file.

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

Imports System

Imports System. I

Public Class ThisWorkbook

Private Sub ThisWorkbook_Open () handles Me Open.

Dim Myfolder 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 = Value currCell.

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

Using MyStreamWriter as New StreamWriter ("C:\My documents\importtext_test\" & MyTextFileName)

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

End using

numRow = numRow + 1

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

End while

Finalize

This program uses the following items:

' 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 Myfolder As Excel. Workbook

Dim MyWorksheet As Excel. Worksheet

Dim currCell as Excel. Range

Dim MyTextFileName As String

Dim numRow As Integer

numRow = 6

Set Myfolder = Application. ActiveWorkbook

Set MySheet = MyWorkbook. Sheets (1)

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

Do until currCell. Offsets (numRow, 0) = vbNullString

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

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

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

numRow = numRow + 1

Loop

This program uses the following article:

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

End Sub

Sub SetFileAndText (fileName, matches)

Add Microsoft Scripting Library to the project references.

Dim fso as new FileSystemObject

Dim stream as a TextStream

Dim myText As String

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

stream.Write matches

stream.Close

End Sub

To write this code, I consulted the following article:

-Eric Schmidt

Eric Schmidt is a programming writer for Visio.

View the original article here