Select one or more text files in a directory.

[gjsala]

Thanks in advance for any information. In VBA I'm looking to select one or more text files from a directory on the harddrive, search the text files for a specific specific word, for example "Test", then when found that line is put into an excel spreadsheet row. There will be many times this key word comes up in the text file.

Thanks!

 

[Cordeyo]

gjsala

I have a text file I must remove quotes from. To do this I use the FileSystemObject. You must have a reference to the Microsoft Scripting Runtime library. This is some of the code I use:

Set oFso = CreateObject("Scripting.FileSystemObject")
Set InStream = oFspenTextFile(sInputFile, ForReading, False)
Set OutStream = oFso.CreateTextFile(sOutputFile, True, False)

Do While Not InStream.AtEndOfStream
Ln = InStream.ReadLine
Ln = Replace(Ln, Chr(34), vbNullString)
OutStream.WriteLine (Ln)
Loop

HTH
Cordeyo

 

[gjsala]

Cordeyo,
I started to use your example code and I got a run time error 5 Invalid procedure call or argument. What do I have to dim or state as a look up location?

Thanks,
gjsala

 

[AnotherHiggins]

What version of Excel are you running?

FileSystemObject has been dropped from 2007.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[PHV]

FileSystemObject has been dropped from 2007
Really ?
I thought it was the FileSearch object ...

 

[Cordeyo]

FileSystemObject still works in Office 2007. At least it does for me in Access. As I said "You must have a reference to the Microsoft Scripting Runtime library". Here is the complete code I use. You will have to change it to suit your needs.



Public Sub RemoveQuotesFromTextFile(ByVal sFile As String, ByVal sPath As String)

Dim oFso As FileSystemObject
Dim ListFile, Ln As String
Dim InStream, OutStream
Dim sWorkFile As String, sTempFile As String

'Check for Souce Files in Working Directory
sWorkFile = sPath & sFile
sTempFile = sPath & "Temp.txt"
ListFile = Dir(sWorkFile)

If ListFile = "" Then
MsgBox ListFile & " is missing!", vbCritical, "Nothing to do."
Exit Sub
End If

Set oFso = CreateObject("Scripting.FileSystemObject")
Set InStream = oFspenTextFile(sWorkFile, ForReading, False)
Set OutStream = oFso.CreateTextFile(sTempFile, False, False)

Do While Not InStream.AtEndOfStream
Ln = InStream.ReadLine
Ln = Replace(Ln, Chr(34), vbNullString)
OutStream.WriteLine (Ln)
Loop

InStream.Close
OutStream.Close

oFso.DeleteFile sWorkFile, True
oFso.CopyFile sTempFile, sWorkFile, True
oFso.DeleteFile sTempFile, True

Set InStream = Nothing
Set OutStream = Nothing
Set oFso = Nothing

End Sub


Cordeyo

 

[AnotherHiggins]

[sheepish]
nevermind
[/sheepish]


Right - that's FileSearch. Sorry.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[fumei]

We would all be in deep doo-doo if FSO was dropped! It is bad enough they dropped FileSearch. I still do not understand why.

gjsala: "What do I have to dim "

Hopefully you are using Option Explicit, in which case the answer to that is...everything, as Cordeyo's code block shows. It always a good idea to explicitly declare everything.

Gerry