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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!


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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.