You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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?"
Sigh.
So here's my problem. I needed to turn this:
... into this:
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 Documents\ImportText_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 = "Office.com 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
.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", "")
Do
' 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
MyReader.Close()
' Enter currCellValue into the spreadsheet
With MySheet.Range("D" & numline)
.Value = currCellValue
.WrapText = True
numline = numline + 1
Next
' Resize the rows and columns in the workbook
.Columns("D").ColumnWidth = 75
.Range(.Cells(1, 1), _
.Cells(numline, 4)).Rows.AutoFit()
.Range("A:D").Columns.AutoFit()
End Sub
End Class
To write this code, I consulted the following articles:
--Eric Schmidt
Eric Schmidt is a programming writer for Visio.
Comments: (7) Collapse
Interesting, but why use Visual Studio? This is an Office blog right?
So we could just use Excel VBA, thusly;
[vb]
' Don't like putting this in the Workbook_Open event
' We probably will want to control when new text is added
Sub ImportTextFiles()
Dim i As Long
Dim strPath As String, strAuthor As String
Dim rngStartCell As Range
Dim MyFiles As Variant
strPath = "C:\My Documents\ImportText_TEST\"
strAuthor = "Eric Schmidt"
Set rngStartCell = Sheet1.Range("A5")
With rngStartCell
.Offset(0, 0).Value = "Office.com contact"
.Offset(0, 1).Value = "File Name"
.Offset(0, 2).Value = "Description"
MyFiles = FileList(strPath, "*.txt")
If TypeName(MyFiles) <> "Boolean" Then
For i = LBound(MyFiles) To UBound(MyFiles)
.Offset(i + 1, 0) = strAuthor
.Offset(i + 1, 1) = MyFiles(i)
.Offset(i + 1, 2) = ImportTextFile(MyFiles(i))
Else
MsgBox "No files found"
End If
With Columns("A:C")
.EntireColumn.AutoFit
.VerticalAlignment = xlTop
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
' Thanks to Chip Pearson see more here
' www.cpearson.com/.../ImpText.aspx
Public Function ImportTextFile(ByVal FName As String) As String
Dim WholeLine As String
Application.ScreenUpdating = False
On Error GoTo EndMacro:
Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
ImportTextFile = ImportTextFile & WholeLine & Chr(10)
Wend
' get rid of trailing
ImportTextFile = Left(ImportTextFile, Len(ImportTextFile) - 1)
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Function
' FileList
' Thanks to Richard Schollar
Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
Dim sTmp As String
Dim sHldr As String
If Right$(fldr, 1) <> "\" Then fldr = fldr & "\"
sTmp = Dir(fldr & fltr)
If sTmp = "" Then
FileList = False
Exit Function
sHldr = Dir
If sHldr = "" Then Exit Do
sTmp = sTmp & "|" & sHldr
Loop
FileList = Split(sTmp, "|")
[/vb]
Yes, this is an Office blog. And yes, you could have done all of these things in VBA, as your code sample shows. That would have been a completely reasonable solution to my problem!
That said, the Microsoft Office developer tools in Visual Studio 2010, the .NET Framework, and .NET languages (VB.NET and C#) are an important part of Office programmability. Many of the professional Visio developers that I know are using Visual Studio and C# to build their Visio solutions. (I, myself, am a VBA programmer who is transitioning over to the .NET languages.)
Using Visual Studio and the Microsoft Office developer tools offers you a wealth of programming tools (msdn.microsoft.com/.../3295w01c.aspx):
- You can more easily hook into other libraries, including all of the System libraries, WPF, Silverlight, etc. etc.
- Visual Studio 2010 has three built-in templates for workbook-level, template-level, and application-level customizations in Excel 2010 or Excel 2007.
- You can deploy multiple solutions as a single package.
- I like being able to declare and initialize a variable or object in a single line of code.
- I really like the ribbon designer in Visual Studio 2010. It makes it really easy to build custom ribbon tabs.
- Garbage collection … is awesome.
A few particular points about the code:
1. Note that I used the Workbook_Open event because I only needed to use this code once. (After I had all of the text files and descriptions into my workbook, I didn't need to run the solution again and could just save the workbook without the code.) You can just as easily use another event … or create a custom Ribbon tab with a button that will run the code in an application-level project.
2. Also, I ran your code and found that it didn't pull in the contents from the text files. That is one of the benefits of using the StreamReader object in the code above: the class has built-in methods for getting text from another file – without having to build your own file reader.
3. Speaking of, VBA has a TextStream object that contains the methods Read, ReadLine, and ReadAll. (See msdn.microsoft.com/.../dd439413(office.12).aspx) You could change your ImportTextFile function like so:
' NOTE: Need to add a reference to the Microsoft Scripting Runtime library to the project.
Dim fso As New FileSystemObject
Dim stream As TextStream
Dim myText As String
Set stream = fso.OpenTextFile("C:\My Documents\ImportText_TEST\" & FName)
myText = stream.ReadAll()
stream.Close
ImportTextFile = myText
Thank you for sharing your code, though! That's the cool thing about programming with Excel - there are multiple ways to do something.
Eric
Hey, Call me old fashioned but
Open a CMD window
change directory to where your files are
Dir *.txt > tmp.txt /B
edit tmp.txt so that it reads (all the files are listed so it wont take long)
copy File1.txt + File2.txt + File3.txt ... + Filex.txt newfile.txt
save as tmp.bat
run tmp.bat
open excel and import newfile.txt
Have a coffee until the programmers write your VBA or Studio code
I was looking to put information from an excel sheet out to a .txt file... is there an easy way to do this? When we do it, it puts a lot of spaces in between the fields... I would like to delete out the spaces automatically... Do you have any ideas with this?
Amy, Eric, and eferraro, Thanks for the posts!
As a VBA programmer, should I be concerned that the office blog is promoting examples in Studio, rather than VBA?
How far into the future will Microsoft continue to invest in VBA?
Yes, you can do this task from the command-line, with VBA, or with Visual Studio. All are equally valid and supported methods for extending, customizing, or automating Excel. The preference is purely on the part of the programmer.
MDillon2218, I think you'll see that the Excel blog is promoting samples in both .NET and VBA. Just look at some of Diego's posts, like his last one about controlling slicers with VBA. We certainly are not giving preference of one technology rather than the other.
jblumho, that sounds like an interesting scenario! Keep an eye out for my next post coming soon ...
Eric, I am patiently waiting to see your next blog update! You have successfully dangled the carrot in front of the rabbit...
Comments: (loading) Collapse