Read Text File into Excel Using VBA or VBScript

Hi All,

I'm a new VBA programmer. I know how to pull an entire text file
into an Excel Spreadsheet, but I only want specific information from
the text file not the entire text file.

What I have is about 25 text files stored in a folder, let's say
C:\test.

Each file is named by a property address as follows:
209 MAIN ST.txt
213 MAIN ST.txt
111 ELM ST.txt
2356 WOOD AVE.txt

On the 11th row of each file is as follows:
Property Address:209 MAIN ST
On the 31st row of each file is as follows:
Total Value:30500

What I would like to do is read each file located in the "C:\test
folder and write a record (row) into a single Excel Spreadsheet for
each property. I would like the Excel Spreadsheet to look as follows
once completed. Note the 1st row below is a header row that needs to
be generated by the code.

Property Address Total Value
209 MAIN ST 30500
213 MAIN ST 60700
111 ELM ST 20400
2356 WOOD AVE 20900

Can I read a header list (in a spreadsheet, text file, or hard coded in
the code) which I would prefer the spreadsheet or text file method,
write the header row in A1 then B1. Next read the 25 text files and
search based on the header info written above (Property Address & Total
Value) and write the appropriate to the single spreadsheet. The 11th
row of the First text file value written in cell A2, then read the 31st
row of the First text file write the value in cell B2, then loop to the
Second text file and values from The 11th row of the Second text file
value written in cell A3, then read the 31st row of the Second text
file write the value in cell B3, so on and so forth until the last text
file is read and the last record is written.

I know this is elementary to most, but I'm a beginner programmer and
sure could use the help...
Can any one help?

Thanks in advance.

Willie T

Willie
Wed Jan 05 11:14:53 CST 2005

Ok, I have a Routine that will read a user defined folder via an
InputBox and get a list of all the files in that folder.

Next I pass that info to a Routine that Reads the Full Text files into
individual Excel spreadsheets, so I've made some progress.

My problems left to resolve:
1. I want to read into one single spreadsheet not 25 (i.e. 25 text
files into a single spreadsheet)
2. I want 1 header line in the one spreadsheet
3. I want only select info out of each text file not the entire text
file.

Can I read the 11th line in each of the text file and import ONLY the
text behind the semicolon?
For example, the 11th line in each file is as follows:
Property Address:209 MAIN ST
I only want to import "209 MAIN ST" from the 11th line in each text
file and place the first entry in A2 of the Excel Spreadsheet, then
read the next file and place that Property Address in Cell A3 until all
text files are read.

Can anyone help or direct me to a group that can.

Code is listed below. Keep in mind that since the code is snippets, it
still need some clean up.

Thanks in advance.

Willie T

Dim MyFileSystemObject As Object 'fs
Dim MyFolderObject As Object 'f
Dim MyFileObject As Object 'f1
Dim MyFileCollection As Object 'fc
Sub LoopThroughInputFiles()
Dim RoutineStartSecondCount As Long
Dim ThisFileFinishSecondCount As Long
Dim AverageSecondsPerFile As Long
Dim StringToDebugPrint As String

RoutineStartSecondCount = Int(Timer) 'int of seconds elapsed since
midnight

FolderContainingRawFiles = InputBox("Enter Name, c/w Path, of Folder
Containing Raw Files")

FileCounter = 0 'initialise

'Dim MyFileSystemObject As Object 'fs
'Dim MyFolderObject As Object 'f
'Dim MyFileObject As Object 'f1
'Dim MyFileCollection As Object 'fc

Set MyFileSystemObject = CreateObject("Scripting.FileSystemObject")
'MyFileSystemObject is a filesystemobject
Set MyFolderObject =
MyFileSystemObject.GetFolder(FolderContainingRawFiles) 'MyFolderObject
is the folder object

Set MyFileCollection = MyFolderObject.Files 'fc is the collection of
file objects in folder object f

For Each MyFileObject In MyFileCollection
FileToWorkWith = MyFileObject.Name
'Now call function/sub to work with file...
'FunctionToOpenAndWorkWithFile
ReadFullTextFile


FileCounter = FileCounter + 1
ThisFileFinishSecondCount = Int(Timer)
AverageSecondsPerFile = (ThisFileFinishSecondCount -
RoutineStartSecondCount) / FileCounter
StringToDebugPrint = FileCounter & " files (of about "
StringToDebugPrint = StringToDebugPrint &
MyFileCollection.Count
StringToDebugPrint = StringToDebugPrint & ") done so far;
time remaining "
StringToDebugPrint = StringToDebugPrint &
Format((AverageSecondsPerFile * (MyFileCollection.Count - FileCounter)
/ 60), "0.0")
StringToDebugPrint = StringToDebugPrint & " minutes"
StringToDebugPrint = StringToDebugPrint & " (average " &
Int(AverageSecondsPerFile)
StringToDebugPrint = StringToDebugPrint & " seconds/file)"
Debug.Print StringToDebugPrint

Next
Debug.Print "File Addition Finished (at last!) " & Date & ", " &
Time
End Sub


Sub ReadFullTextFile()

Dim oExcel As Object
Dim oBook As Object
Dim osheet As Object

Dim filename As String

Set oExcel = CreateObject("Excel.Application")

' Open text file
'filename = "c:\MAIN-ST-205.txt"
'Set oBook = oExcel.Workbooks.Open(filename)
Set oBook = oExcel.Workbooks.Open(MyFileObject)
Set oBook = oExcel.ActiveWorkbook

oBook.Sheets(1).Activate
Set osheet = oBook.Sheets(1)

'Set osheet = oBook.ActiveSheet
' Make Excel visible
oExcel.Visible = True
oExcel.UserControl = True

' save as excel workbook
'filename2 = "c:\MAIN-ST-205.xls"
filename2 = (MyFileObject) & ".xls"
oBook.SaveAs filename2, 1

' ***** At this point I would like to run a macro, however they are
'not available in the macro window or within this code.
Set oExcel = Nothing
Set oBook = Nothing

'End
End Sub