Opening multiple text files in a folder and appending to another

acollins

Board Regular
Joined
Aug 1, 2007
Messages
63
I'm going to be receiving data via FTP to a certain folder that I need to open and append to a holding file (another text file), which I will then import into Excel, parse, reformat and save to yet another text file for import into another application. I don't know how many files are going to be in the designated folder at any given time or what their names are going to be (only that there could be as few as one and they will have a .txt extension). I know how to find and read in a single file name into a variable, but I'm not sure how to do it with multiple files (I suspect I will have to read them into an array or something, but I am not sure how to do this). I also know how to open to text files - 1 as source and 1 as target and append lines from source to target. I have the routine already written to import the "holding" file, reformat and append to the import file and it works with just one text file at a time. I also plan to write a "clean-up" routine to remove the text files once they have been successfully posted. That way, the assumption can be made each time the routine is run that any new text files in the folder will not have already been posted. It's mostly the identifying and reading in the multiple filenames that I need to process that I need help with.

Thanks in advance for any help,

Alan
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi,

with the following code you can select several text files. These are then in "C:\Temp\All.txt" collected.

If all files of a listing to be read in you can also use "msoFileDialogFolderPicker" (starting from Excel XP).

Caution - the read in files are deleted - thus tries it at a few copies.

Code:
Option Explicit

Private Declare Function GetCurrentDirectory Lib "kernel32" _
    Alias "GetCurrentDirectoryA" (ByVal nBufferLength&, ByVal lpBuffer$) As Long
  
Private Declare Function SetCurrentDirectory Lib "kernel32" _
    Alias "SetCurrentDirectoryA" (ByVal lpPathName$) As Long

Public Sub TXT_Read()
    Dim strSourceFile As String
    Dim varFile As Variant
    Dim strDirOld As String
    Dim strContents As String
    Dim IntTMP As Integer
    On Error GoTo TXT_Read_Error
    strDirOld$ = String(255, 0)
    Call GetCurrentDirectory(255, strDirOld$)
    strDirOld$ = Left(strDirOld$, InStr(1, strDirOld$, vbNullChar) - 1)
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = "C:\"
        .Title = "Multiselection of files"
        .Filters.Add "Text files", "*.txt; *.csv", 1
        .ButtonName = "Selection..."
        .InitialView = msoFileDialogViewList
        .AllowMultiSelect = True
        If .Show = -1 Then
            For Each varFile In .SelectedItems
                Reset
                IntTMP = FreeFile
                Open varFile For Binary As #IntTMP
                strContents = Space$(LOF(IntTMP))
                Get #IntTMP, , strContents
                Close #IntTMP
                TXT_Write (strContents)
                Kill varFile ' CAUTION - file IS deleted!!
            Next
        Else
            MsgBox "No file was selected!"
            Call SetCurrentDirectory(strDirOld$)
            Exit Sub
        End If
    End With
    On Error GoTo 0
    Exit Sub
TXT_Read_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    Call SetCurrentDirectory(strDirOld$)
End Sub

Public Sub TXT_Write(strContents As String)
    Dim strFileName As String
    Dim IntTMP As Integer
    strFileName = "C:\Temp\All.txt" ' adapt
    IntTMP = FreeFile
    Open strFileName For Append As #IntTMP
    Print #IntTMP, strContents
    Close #IntTMP
End Sub
Case_Germany
 
Upvote 0
Thanks for the code, Case_Germany, but I was hoping to have the macro go to a designated folder and determine the number of files with a .txt extension and read their names into variables or an array. I do not wish to make the end user select the folder and files themselves. I am sorry if I wasn't clear on that point.

I don't know how to insert VBA code into one of these posts like you did, but here's a link to a thread where Norie recently helped me with code to read a file name into a variable in the case where there will always be just one file in the folder (always the same folder):

http://www.mrexcel.com/forum/showthread.php?p=1645085#post1645085

What I want to do is to adapt that code to read in multiple files (also always in the same folder). The other difference, of course, is that these are text files instead of workbooks. Then I want to process them one at a time as mentioned in my previous post. Hopefully this makes it a little clearer what I am trying to accomplish.
 
Upvote 0
Hi. I believe I have found the code I need to handle multiple text files. I believe that if I enclose my code to append text files within a Do loop that starts with <b>Do Until strFile = ""</b>, with <b>strFile = Dir</b> at the end, then it should do what I want it to do.

Thanks for the help.
 
Upvote 0
OK. So I have incorporated the code mentioned in my last post into my append routine. It does exactly what I need it to do except for one little glitch - it leaves a blank line between records in the destination file every time a new source file is opened and writes to it. My code is below. Is there a little tweak I can make to my code to eliminate the blanks? BTW - I have also tried closing and reopening the dest file with every new source file, but I still get the same results.

<style type="text/css">br /> <!--
body {background-color: #ffffff; color: #000000; font-family: "Courier New"; font-size: 10pt}
.keyword {background-color: #ffffff; color: #000080}
.comment {background-color: #ffffff; color: #008000}
-->
</style>
Code:
Private Sub GetTextFiles()
Dim strFile As String

    strFile = Dir("Y:\Shoebuy\PO\*.txt")
    DestNum = FreeFile()
    Open "Y:\Shoebuy\test.txt" For Append As DestNum
 
    Do Until strFile = ""
        SourceNum = FreeFile()
        Open "Y:\Shoebuy\PO\" & strFile For Input As SourceNum
 
                Do While Not EOF(SourceNum)
                    Line Input #SourceNum, Temp
                    Print #DestNum, Temp
                Loop

            Close #SourceNum
            strFile = Dir
     Loop
   Close #DestNum
End Sub
Sorry for all the extra spaces. When I copied and pasted my HTML code, it inserted them and I am not sure how to remove them.

Alan

[cleaned up by Admin]
 
Last edited by a moderator:
Upvote 0
Hi again,

try:

Code:
'......
Do While Not EOF(SourceNum)
    Line Input #SourceNum, Temp
    If Trim(Temp) <> "" Then
        Print #DestNum, Temp
    End If
Loop
'......
Case_Germany
 
Upvote 0

Forum statistics

Threads
1,221,586
Messages
6,160,645
Members
451,661
Latest member
hamdan17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top