VBA Importing .txt Files WITH FileNames

[jdmeagher]

Hi all -

I have several thousand text files I have put in a single folder I need to compile in a single workbook, which I am able to do easily using the below code.
I am also easily able to extract the FileNames of all the files in that folder with the code below that.

What I maddeningly can't seem to do is do both - combine the code below such that it will take extract the data in one file line-by-line and then in a NEW column at the end of each row, paste the file name.

Essentially, I want all of the files in the folder compiled in a new workbook, but I want an additional column so I can sort/identify the data by the original .txt file from which it came.

Unfortunately, in these original files, there is no unique identifier INSIDE the data file - the only unique identifier is in the FileName.

Any help or strategies would be greatly appreciated.

Extracting Files:
Sub ReadFilesIntoActiveSheet()
Dim fso As FileSystemObject
Dim folder As folder
Dim file As file
Dim FileText As TextStream
Dim TextLine As String
Dim Items() As String
Dim i As Long
Dim cl As Range

' Get a FileSystem object
Set fso = New FileSystemObject
' get the directory you want
Set folder = fso.GetFolder("W:\WorkStuff_Rpts\WorkStuff_Folder" )
' set the starting point to write the data to
Set cl = ActiveSheet.Cells(1, 1)


' Loop thru all files in the folder
For Each file In folder.Files

Set FileText = file.OpenAsTextStream(ForReading)

' Open the file
Set FileText = file.OpenAsTextStream(ForReading)
' Read the file one line at a time
Do While Not FileText.AtEndOfStream
TextLine = FileText.ReadLine
' Parse the line into | delimited pieces
Items = Split(TextLine, "|")
' Put data on one row in active sheet
For i = 0 To UBound(Items)
cl.Offset(0, i).Value = Items(i)
Next
' Move to next row
Set cl = cl.Offset(1, 0)
Loop


' Clean up
FileText.Close
Next

Set FileText = Nothing
Set file = Nothing
Set folder = Nothing
Set fso = Nothing
End Sub


Extracting File Names:


Sub GetFileNames()

Dim xRow AsLong
Dim xDirect$, xFname$, InitialFoldr$

InitialFoldr$="G:\"'<<< Startup folder to begin searching from

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath &"\"
.Title ="Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If.SelectedItems.Count <>0Then
xDirect$=.SelectedItems(1)&"\"
xFname$= Dir(xDirect$,7)
DoWhile xFname$<>""
ActiveCell.Offset(xRow)= xFname$
xRow = xRow +1
xFname$= Dir
Loop
EndIf
EndWith
EndSub

[ItsMe]

Right after this line in your first procedure, the one with TextStream, you should be able to access the name property of your file object.
For Each file In folder.Files

so
file.name


Also, I would not use file as a variable name. Maybe fsoFile or anything other than file.
Dim fsofile As file

[jdmeagher]

Thanks, itsme.

Using fsofile.Name (having changed file to fsoFile As file) I get "Compile error: invalid use of property".

Any suggestions?

[ItsMe]

...
Any suggestions?

Post the code you are using and point out which line is throwing the compilation error.

[jdmeagher]

The line I have in bold/italicized is what generates the "Compile error: invalid use of property." Specifically, the .Name.

Sub ReadFilesIntoActiveSheet()
Dim fso As FileSystemObject
Dim folder As folder
Dim fsofile As file
Dim FileText As TextStream
Dim TextLine As String
Dim Items() As String
Dim i As Long
Dim cl As Range

' Get a FileSystem object
Set fso = New FileSystemObject
' get the directory you want
Set folder = fso.GetFolder("W:\workthings")
' set the starting point to write the data to
Set cl = ActiveSheet.Cells(1, 1)
' Loop thru all files in the folder
For Each fsofile In folder.Files

'Read in file name
fsofile.Name

' Open the file
Set FileText = file.OpenAsTextStream(ForReading)
' Read the file one line at a time
Do While Not FileText.AtEndOfStream
TextLine = FileText.ReadLine
' Parse the line into | delimited pieces
Items = Split(TextLine, "|")
' Put data on one row in active sheet
For i = 0 To UBound(Items)
cl.Offset(0, i).Value = Items(i)
Next
' Move to next row
Set cl = cl.Offset(1, 0)
Loop

' Clean up
FileText.Close

Next

Set FileText = Nothing
Set fsofile = Nothing
Set folder = Nothing
Set fso = Nothing
End Sub

[ssanfu]

Ummm, you do realize this is an Access forum, not an Excel forum..... right?

Anyway, you have

fsofile.Name

You can't just have that as a line. You should do something like

Dim txFileName as string

txFileName = fsofile.Name

(this might not work - just an example - I don't have much experience with using File system Object. )


The point is, you want to get the file name into a variable so you can add it to the current row, before moving to the next row. So you have a variable to hold the file name.

Wish I could help more. I use a different method to open text/csv files. I loop through the folder using Dir() to get the files.

[ItsMe]

Here, try this code in a button click event. Obviously, you will want to change the value of strFolderPath to a valid folder path that only has a few files inside of it.

Dim fso As New FileSystemObject
Dim fsoFolder As Folder
Dim fsoFile As File
Dim strFolderPath As String
Dim strFileName As String

strFolderPath = "C:\Test\ExcelFiles"
Set fsoFolder = fso.GetFolder(strFolderPath)

For Each fsoFile In fsoFolder.Files
    strFileName = fsoFile.Name
    MsgBox strFileName
Next fsoFile

Also, you missed changing the variable named file to fsoFile in one place.
Set FileText = fsoFile.OpenAsTextStream(ForReading)