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?"
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.
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
.Range ("A5").Value = "Contact Office.com"
.Range ("B5").Value = "Filename"
.Range ("C5").Value = "Title"
.Range ("D5").Value = "Description"
«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
.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", "")
«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
' Enter currCellValue into spreadsheet
With MySheet .range ("D" & numline)
.Value = currCellValue
.WrapText = True
numline = numline + 1
«Resize rows and columns of the workbook
.Columns ("D").ColumnWidth = 75
.(Range.Cells (1, 1) _
.(Numline, 4) of cells).AutoFit () .rows.
.Range ("A:D").Columns AutoFit ().
To write this code, I consulted the following articles:
Eric Schmidt is a programming writer for Visio.