Merge Files part 2

A follow up to this question: 

How could I modify this code to handle a variable number of files with similar naming convention.  For example I may run the code for FILE01.TXT thru FILE04.TXT, and run the code again for FILE01.TXT thru FILE09.TXT.
Rather than adding to / Removing from the code, is there a way to handle this?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

jjmartinConnect With a Mentor Commented:
Something like this perhaps:

MyPath = "c:\mydirectory"   ' Set the path.
myname = Dir(MyPath, vbDirectory)   ' Retrieve the first entry.
Do While myname <> ""   ' Start the loop.
   If Left(4, myname) = "FILE" Then
        'Do your file merge
   End If
   myname = Dir   ' Get next entry.

This will retrieve all the files from a given directory with a file name that matches the pattern you are looking for (i.e. starts with FILE).
try something like this...

Dim filname As String
Dim num As Integer
filname = "C:\file"
For num = 1 To 3
filename1 = filname & LTrim(Str$(numb)) & ".txt"
Next num
Use the dir() function to retrieve the list of files within a given directory.
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


If you add the following code to your project, you will be able to accomplish your task with one line of code.

I have provided two examples at the bottom of this message.

'*** START Code ***

Sub AppendToFile(ByVal strSource As String, ByVal strDestination As String)

Dim strVar As String

Open strSource For Input As #1
Open strDestination For Append As #2

Do While Not EOF(1)
  Input #1, strVar
  Print #2, strVar

Close #2
Close #1

End Sub

Sub GetFiles(ByVal strFolder As String, ByVal strFileSpec As String, ByVal strDestination As String)

'This sub finds all the files in strFolder that match strFileSpec
'strFileSpec can contain wildcard characters  ie  File*.txt
'It then appends the contents of each file into strDestination

Dim strFileName As String

'Ensure the folder name ends with \
If Right$(strFolder, 1) <> "\" Then
    strFolder = strFolder & "\"
End If

'See if any files in strFolder match strFileSpec
strFileName = Dir(strFolder & strFileSpec)

If Len(strFileName) > 0 Then
    'If a file is found, ensure that it isn't the destination file
    If strFolder & strFileName <> strDestination Then
    'It's not the destination file so append it to the destination file then
    'check for the next file which matches the file specification
    AppendToFile strFolder & strFileName, strDestination
    strFileName = Dir()
    GoTo CheckIfFileExists
    End If
End If

End Sub

'*** END Code ***

You call the GetFiles routine and specify the folder to look in, the file specification you want to search for, and the destination file that you want to append to.

If all your files are in your My Documents folder, you could can use the following line of code:

GetFiles "C:\My Documents", "File*.txt", "C:\My Documents\FileAll.txt"

or, if you only want to append a single file, you can use this line:

GetFile "C:\My Documents", "File01.txt", "C:\My Documents\FileAll.txt"

I hope you find this useful.
The second example should call GetFiles rather than GetFile.
grnavarroAuthor Commented:
I used JJMartin's code and with some slight modification it got the job done.  I'm sure your code works also, but I need to give the points where they're deserved.
grnavarroAuthor Commented:
In case anyone cares, here's the final answer:
Do While FileName <> ""
    iReturn = InStr(FileName, "DupntPyr")   'look for PO files
    FileName2 = "C:\POftp\" & FileName      'Add Directory to filename
    txtDisplay.Text = FileName2             'Display filename
    If iReturn = 1 Then                     'If PO file Found start loop
        Open FileName2 For Input As #1
        If iDummy <> 1 Then                 'Test control variable
            Open "c:\POftp\OneFile.txt" For Output As #2
        End If
        iDummy = 1                          'Set dummy variable to control
                                            'Opening of output file
        Do While Not EOF(1)
            Input #1, strVar
            Print #2, strVar
        Close #1                            'Leave output file open
    ElseIf iReturn = 0 Then                 'No PO files Found
        MsgBox "PO Files not found!"
        Unload frmMain
        Exit Sub
    End If
    FileName = Dir
Close #2
txtDisplay.Text = "Now Press FTP Button"
End Sub
All Courses

From novice to tech pro — start learning today.