Paste the text manually in a workbook? Absolutely no!

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

Shortly after, my colleague asked me if I had all the descriptions for the models. "Yes," I say, "these are included in that shared folder with templates. Each description is saved as a text file. "

Puzzled, my colleague asks, "Um, might put all descriptions in a single Excel workbook?"

Sigh.

So here is my problem. I needed to turn this:

 

... in this:

 

Overall, the folder was about 40 text (.txt) file that I needed to fill in your working folder, where the content would be listed out line by line on a single worksheet. From left to right, each line is necessary to include my name, the name of the PowerPoint template files, template title (the filename cleaned up a bit) and description to 255 characters.

Sure, you may have typed the name of the file in the second column, he wrote a formula in the third column to translate the name of the file in a title and then copied and pasted from the text file within the workbook. Or, if so inclined, I could simply use the command From text in group to get external data data card.

This type of time creating a workbook to spend? Dandeng!

Instead, I wrote some Visual Basic code to extract the data that I need from the folder with the PowerPoint file and text, and then enter in the workbook.

To get the text from .txt file in the Excel workbook, the code uses the directory.GetFiles method and a search template to only open text files in the specified folder. Enter the name of the text file in the next empty row in column b of the active sheet. Then open any text file with a StreamReader object and then adds each line of text from that file into a string variable. Once each line of text in the file is written to the string, the string is added to column d in the same row. Therefore, the line counter is incremented to move the next line and repeats the process until it transcribed each txt. files in the specified folder.

Here's the code I used. Is a project-level workbook by using Visual Basic, Visual Studio 2010. network 4 and Excel 2010.

Imports Microsoft.VisualBasic.FileIO

Imports System.io

Imports System

Public class ThisWorkbook

Private Sub ThisWorkbook_Open () handles me.load ' Open

' Create an array where each element is a text file in the folder

Dim MyFiles As String() = directory.GetFiles ("C:\My documents\importtext_test", "txt")

Dim MyFile As String

Dim MyWorkbook as Microsoft.Office.Tools.Excel.workbook = Me.Application.ActiveWorkbook

Dim MySheet as Excel.worksheet = Me.Application.ActiveSheet

' Set the header rows

With MySheet

.Range ("A5").Value = "Contact Office.com"

.Range ("B5").Value = "Filename"

.Range ("C5").Value = "Title"

.Range ("D5").Value = "Description"

Ends with

«Create a counter for the rows in the workbook

Dim numline As Integer = 6

For each MyFile In MyFiles

«Create a StreamReader

Dim MyReader as StreamReader = New StreamReader (MyFile)

Dim currLine As String

Dim currCellValue As String = ""

' String MyFileName includes directory

«The name of the text file begins with the character 32

' Enter the file name and the name of the template spreadsheet

With MySheet

.Range ("A" & numline).Value = "Eric Schmidt"

.Range ("B" & numline).Value = _

MyFile string.substring (32).Replace (".txt", "potx")

.Range ("C" & numline).Value = _

MyFile string.substring (32).Replace ("_", "").Replace (".txt", "")

Ends with

Do

«Read every line containing text and add to the string currCellValue

currLine = MyReader console.ReadLine ()

currCellValue = currCellValue + "" + currLine

CurrLine Loop Until you nothing

«Close the text file

Myreader.close()

' Enter currCellValue into spreadsheet

With MySheet .range ("D" & numline)

.Value = currCellValue

.WrapText = True

Ends with

numline = numline + 1

Next

«Resize rows and columns of the workbook

With MySheet

.Columns ("D").ColumnWidth = 75

.(Range.Cells (1, 1) _

.(Numline, 4) of cells).AutoFit () .rows.

.Range ("A:D").Columns AutoFit ().

Ends with

End Sub

End Class

To write this code, I consulted the following articles:

--Eric Schmidt

Eric Schmidt is a programming writer for Visio.

View the original article here