Sub ProcessTextFiles()
' You must be on the sheet that has the files you want to process (otherwise you need more code)
' Read All Text files listed in column D (row) and put all text from file into Column J (row)
Dim iCount As Long ' for loping through all rows of column D
Dim sProcessFile As String ' full filename and path
Dim sFilesPath As String ' file path of text files (could hardcode but this offers flexibility)
Dim strFileContent As String ' text file to process - this comes from extracting the name from column D & (row)
sFilesPath = "C:\CATTEXT\" ' put file path of the text files here
iCount = 2 ' Start after the headings ' if your spreadsheet has headings iCount should start at 2 otherwise start at 1
Cells(iCount, 4).Select ' Get the filename from the first selection
' Check all files exist before processing - uses function by Allen Browne. http://allenbrowne.com June, 2006.
Do While Selection.Value <> "" ' Stop processing when there are no more rows with filenames in column D
If Trim(Selection.Value & " ") = "" Then Exit Do ' double check just in case the column D row has a space in it
sProcessFile = sFilesPath & Trim(Selection.Value & " ") ' build the full file/path to open
If Not FileExists(sProcessFile) Then
MsgBox "FileName " & sProcessFile & " was not found - Please fix and Restart"
End
End If
iCount = iCount + 1
Loop
' Restart the Starting position to proecss the Text files
iCount = 2 ' Start after the headings ' if your spreadsheet has headings iCount should start at 2 otherwise start at 1
Cells(iCount, 4).Select ' Get the filename from the first selection
Do While Selection.Value <> "" ' Stop processing when there are no more rows with filenames in column D
If Trim(Selection.Value & " ") = "" Then Exit Do ' double check just in case the column D row has a space in it
sProcessFile = sFilesPath & Trim(Selection.Value & " ") ' build the full file/path to open
If Not FileExists(sProcessFile) Then
MsgBox "FileName " & sProcessFile & " was not found - Please fix and Restart"
End 'you can end here or do some sort of process
End If
Open sProcessFile For Input As 1 ' Open the currently selected file
Cells(iCount, 10).Value = Input$(LOF(1), 1) ' read all of the data and place it into column J of the same row
Close #1 ' you are done with this file so close it
iCount = iCount + 1 ' increment your row pointer
Cells(iCount, 4).Select ' select the new row column D
Loop
Finished:
MsgBox "All of the text files have been processed"
End Sub
Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
'Purpose: Return True if the file exists, even if it is hidden.
'Arguments: strFile: File name to look for. Current directory searched if no path included.
' bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
'Note: Does not look inside subdirectories for the file.
'Author: Allen Browne. http://allenbrowne.com June, 2006.
Dim lngAttributes As Long
'Include read-only files, hidden files, system files.
lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
If bFindFolders Then
lngAttributes = (lngAttributes Or vbDirectory) 'Include folders as well.
Else
'Strip any trailing slash, so Dir does not look inside the folder.
Do While Right$(strFile, 1) = "\"
strFile = Left$(strFile, Len(strFile) - 1)
Loop
End If
'If Dir() returns something, the file exists.
On Error Resume Next
FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
End Function