File Dialog-Browse/Save/Append

First of all, I still can't quite understand the logic of the first section of code that testing file names against checkfile, but beyond that in the code looking for a file in the folder

For i = LBound(CheckFile) To UBound(CheckFile)
FileFound = Dir(MyPath & "\" & CheckFile(i))

You are using MyPath which is the first time this appears in the code, you do set a path but it is this line that I have modified

sDest = "C:\Users\A C\Downloads" ' will be changed to CurrentProject.path & MAXIMOExports

So I am not sure how the code will find anything, even though the code does not provide any Path not found error messages, which I would have thought it would but does give a file not found error.
OK, I have the code for finding the files in the desired directory, this finds files starting with the second item in my checklist BlankDB

CheckFile = Array("Experiment*.*", "BlankDB*.*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")
On Error GoTo errorcatch

'The following lines of code use a network path for the source file :
sDest = "C:\Users\A C\Downloads\" & CheckFile(1) ' will be changed to CurrentProject.path & MAXIMOExports
Dim ctr As Integer
ctr = 1
FileFound = Dir(sDest) ' Retrieving the first entry.

Do Until FileFound = "" ' Start the loop.

MsgBox ctr & " - " & FileFound
ctr = ctr + 1
FileFound = Dir() ' Getting next entry.
Loop

I am just using ctr to count the number of files found in the folder.

Is it possible to place the Checklist file names in an Access table with the first (master) file names, so that that the system knows which file to append to?

MyPath is an error and should be sDest. I copied the code over incorrectly. As far as putting the master file names in a table, I could put the string that will be in every occurrence of the file but not the entire filename. As that will change for each file. For instance, it could be TESTMAIN070717 or it could be TESTMAIN1234 or it could be TESTMAIN54764. The file will always have TESTMAIN in the filename. That filename structure is applicable to all the file strings I have listed in Checkfile. I am trying to use Checkfile to look for existing files in a directory. If the file in the listbox is TESTMAIN071417 and there is a TESTMAIN072517, Checkfile should detect that and append the selected file in the listbox to the existing file in the directory.

Currently, the routine will take 1 file at a time from the listbox and run Checkfile to see if an existing file with matching string exist. It will detect if the file does exist and will open the selected file from the listbox. I cannot open the file found from Checkfile in the directory. I’m fine with changing my approach if the logic is not right and there is a better way. The overall intent is to take a file from the listbox and copy it to another directory. If file in the listbox contains TESTMAIN in the filename and a file in the directory contains TESTMAIN in its filename, merge the file from the listbox to the existing file in the directory. I know I’m being repetitive and it’s not because I don’t think you understand what I’m trying to do. I’m just wanting to make sure what I am asking for is understood and there in no confusion. It’s not to be condensing. FYI, I haven't ran the code you provided but will later tonight. Thanks again.

So did you run my code, which does find the file names if they are there?.
The idea about the table is to identify which file is the one to be appended to and use that instead of of the checklist, the problem with the checklist is that it is hard coded in to the VBA and needs a programmer to change or add any new files.

I just now ran your code. It does find the file names if existing. Thanks for providing that. The checklist is hard coded but it will not change. The TESTMAIN, TESTPRIMARY, and so on used in the checkfile filename will never change. That portion of the filename will be in every occurrence of a file. TESTMAIN123 or TESTMAIN456 or 7879TESTMAIN. The files will always be generated with one of the strings used in Checkfile. So I don't see how I can use a table to identify the filenames. The only thing for certain about the filename is that TESTMAIN, TESTPRIMARY, and so on will be in the filename.

I guess I could take what you provided and somehow use a few if statements. If file from listbox contains TESTMAIN and file in directory contains TESTMAIN, open and append. Is that a good route to go or some other way better. It would be quite a few if's. I was attempting to get around that by using the checkfile. Since the files in the listbox are being copied one file at a time. I was attempting to take that file and before FileCopy, run checkfile, and if the checkfile and listbox filename shared matching strings perform the open and append.

Thanks for the code.

Andy, I will work on the rest of the code now that I have it finding the files.
Is it an incorrect assumption to think that normally you would be handling just one new file at a time?

I am sorry to belabour this point but WHY does your organisation allow the proliferation of so many different versions of the same file names, this is IT Chaos, (or do you get the data in from other people)?
That then leads to the second question WHY does your organisation put up with it, why hasn't someone come up with a procedure or protocol to rename the files in a logical fashion?
That also leads to the question of why isn't the data imported in to Access Tables and then totally ignored in some archive somewhere, as the total table can be output to a single file at any time.

OK, I am not sure of this helps or not, as I think that the code that I supplied doees most of what you want, ie find the CheckFile that matches your list file, but I now have the code to establish the date of the file and the routine will which is the oldest file in the folder with the Checklist name.
This is the code

Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As String
Dim sDest As String, strFileTemp As String, FileExist As String, filed As Date, firstfile As String, firstfiled As Date
Dim ctlList As Control, varItem As Integer, posn As Integer, FileName As String
CheckFile = Array("*TESTMAIN*.*", "*TESTCASE*.*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")
On Error GoTo errorcatch

'The following lines of code use a network path for the source file :
sDest = "C:\Users\A C\Downloads\" & CheckFile(0) ' will be changed to CurrentProject.path & MAXIMOExports
MsgBox sDest
Dim ctr As Integer
ctr = 1
FileFound = Dir(sDest) ' Retrieving the first entry.
If FileFound <> "" Then
firstfile = FileFound
filed = FileDateTime("C:\Users\A C\Downloads\" & FileFound)
firstfiled = filed
MsgBox ctr & " - " & FileFound & " dated - " & filed
End If

Do Until FileFound = "" ' Start the loop.

ctr = ctr + 1
FileFound = Dir() ' Getting next entry.
If FileFound <> "" Then
filed = FileDateTime("C:\Users\A C\Downloads\" & FileFound)
If filed < firstfiled Then
firstfiled = filed
firstfile = FileFound
End If

MsgBox ctr & " - " & FileFound & " dated - " & filed
End If
Loop
MsgBox firstfile & " dated - " & firstfiled
Exit Sub


So I assume that having established the correct master file you will open it for appending, open your List file for reading and append it's data to the master file. Or if no file is found you will just copy the list file in to the folder with the extension new "text.dat" name.

In some cases there will may be only one new file at a time but there will be instances where there is a new file for each of the five files.

I understand the questions and it is a lot of trouble to move files around when you could import them directly to Access. In that case, if you already had the master file in Access it would be seamless to import/append the new records to the existing table. The file structure is something I can't change so I don't fight that. Just to clarify, what is being called the master file would contain a set of data and a second file of the same type would contain completely different data. The second file isn't a revised version of the first. The first file could have automotive parts/manufacturing from 100 cars and live in the directory. Then another export is done of 100 different cars. For every export of that file type, it is desired they all live in one file. Then, import the one file. But Access could handle all of this 1 file at a time with ease. I wanted that method but didn't get it.

I'll add the code you provided to the other part of code I have and see what happens. I appreciate your effort in this. I was hitting a wall.

Correct. If master file exist, open it for appending and open file from listbox for reading and append it's data to the master file. FileCopy for no matching file type.

Was the code you provided finding more than one file for you? It only sees the first entry in the directory for me? It looks like everything is in place to find each entry but it ins't for me.

That code finds the oldest file with first item name in the Array, you would need to loop through the array to get the oldest versions of the others.
That is why I said I wasn't sure whether or not helped with what you were trying to do.
I was trying to identify the Master file for you.

I understand. I may be missing the point of what it does but since there will be only one instance of each file type in the directory the date shouldn't matter. There will only be one TESTMAIN, TESTPRIMARY, and so on. If you are copying another file from the listbox of that same file type is where the open and append routine should take place. I could be missing the obvious so just point it out if I am. I think I can the previous version you showed and look for the files and hopefully work some logical operators in there to open and append to the files. Thanks for the response.

No you aren't missing the point, I thought from your previous posts that there were all sort of files with variations of TESTMAIN etc, but if you do not have trouble identifying the original then as you say it is time to open that file for appending and opening the new file for reading.
So what logical operators do you need help with can you give me a simple example and I will advise accordingly.

My apologies. I could have been more clear in that respect. I have it set up to take one file at a time from the listbox and copy it to location sDest. If the file in file from the listbox is TESTMAIN and the file found piece you provided finds a TESTMAIN master, then append data from listbox TESTMAIN to master TESTMAIN in the directory sDest. Perform that operation for each file in the listbox. If no master file found, copy file to directory. I copied the code to this thread of the copy file portion. That's what I need to add this bit to. I can copy it here again if that would help.

I would have responded sooner but have been away. Thanks for the guidance.

I will try and take a look at it tomorrow nd get back to you.

OK that will work.

Well I should have done it this way in the first place, sorry it has taken me so long to get here.
But working from the beginning I have gone through step by step.

This code which includes some of yours and some of mine does the basics, it tests if the list file is in the array, if it is it appends the data to the file found in the folder, if it isn't it copies the file to the folder where the array files are.
Now I am not sure this does what you want in terms of finding the Actual Master files.
You seem to know which ones they are, but I don't, if it was me I would place the known master file's paths in a table in the database and refer to them in that way, because takes away the uncertainty of whether it is the correct file.

It doesn't include the part of your code that renames the file to temp for copying as I am not sure of the logic behind it.

But at least I have got it this far, the rest should just be finessing what we have to include your equirements.

Here is the code, as usual you do not the message boxes which are there to show the progress through the code.

Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As String, sOrigin As String, datastring As String
Dim sDest As String, strFileTemp As String, FileExist As String, filed As Date, firstfile As String, firstfiled As Date
Dim ctlList As Control, varItem As Integer, posn As Integer, FileName As String, result As Integer, fileinarray As Integer
CheckFile = Array("TESTMAIN", "TESTCASE", "TESTPRIMARY", "TESTSECONDARY", "TESTALTERNATE")
On Error GoTo errorcatch
Set fs = CreateObject("Scripting.FileSystemObject")
'The following lines of code use a network path for the source file :
sDest = "C:\Users\A C\Downloads\TestFolder\" ' & CheckFile(0) ' will be changed to CurrentProject.path & MAXIMOExports
sOrigin = "C:\Users\A C\Downloads\TestFolder2\"
'MsgBox sDest & " " & sOrigin
Set ctlList = Me.List0
For varItem = 0 To ctlList.ListCount - 1
For x = 1 To Len(ctlList.ItemData(varItem))
' Parse filename only
If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
Next x
firstfile = ctlList.ItemData(varItem)
FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
'MsgBox varItem & " - " & FileName
result = 0
fileinarray = 0
For i = LBound(CheckFile) To UBound(CheckFile)
result = InStr(1, ctlList.ItemData(varItem), CheckFile(i))
'MsgBox ctlList.ItemData(varItem) & " - " & CheckFile(i)
If result <> 0 Then
MsgBox result & " - File is in array append it"
fileinarray = 1
FileFound = Dir(sDest & CheckFile(i) & "*.*")
MsgBox FileFound

Open sDest & FileFound For Append As #2
MsgBox firstfile
'Open sSourceFile For Input As SourceFileNum
Open firstfile For Input As #1
MsgBox 2
Do Until EOF(1)
Line Input #1, datastring
MsgBox datastring
Print #2, datastring
Loop
Close #2
Close #1
Exit For
End If
Next i
If fileinarray = 0 Then
MsgBox result & " - file is not in array copy it"
FileCopy ctlList.ItemData(varItem), sDest & FileName
End If
Next varItem
Exit Sub
errorcatch:
Close #2
Close #1
MsgBox "Error - " & Err.Description

Thanks for your help first of all. It is much appreciated. This is working as intended expect at FileFound. It breaks down for me at line below - FileFound. The files are exported as dat files. So strFileTemp replaces the dat extension with .txt extension. VBA can not open a dat (or can it?). So when the master file is copied it will copy the dat file as well as change the extension and create a txt file. Then when you are copying the a second file of that file type the FileFound should look to find the Checkfile .txt file. That's where the disconnect is now. I tried adding the ".txt*" filter but that doesn't seem to work. Just to clarify the Checkfile does find the existing files and the Msgbox confirms that. Just cannot open the master file because FileFound is empty.

FileFound = Dir(sDest & "\" & CheckFile(i) & "*_Temp.txt*")
MsgBox FileFound


Open sDest & FileFound For Append As #2
MsgBox firstfile

First of all check what
CheckfFile &"*_Temp.txt*"
actually gives you as a file name.
Does that filename actually exist in the folder sDest?
I am not sure why you would need the astrix on the end.

It does exist. I was attempting to explain that the very first file that is copied to sDest is .dat file extension. Since I didn't think VBA could open, read, and write to that type of file I copy the .dat as well as create a Temp.txt. Then when a second or third file of that type (TESTMAIN) is copied to the directory it should find the TESTMAIN.txt file and append to that one. This is all messy I know. I didn't think I could open and read anything from the .dat file. Let me mess around with it and see what's going on. Maybe you can and this all not needed.

I'm new to this thread and only skimmed all the previous posts. but as I understand the situation...
You have a "NewFiles" Directory, (FixedPath,) Which Must Not be Changed, and an "AccessFiles" directory, NewPath, which does change from time to time.

All files have one of =Array("TESTMAIN", "TESTCASE", "TESTPRIMARY", "TESTSECONDARY", "TESTALTERNATE") Strings in their Names.

For each file in "NewFiles," if a file with the same inner String exists in "AccessFiles, Append, else Move.

Personally, I would not list any files in a Form Listbox. I might show the current operation in the Status Bar.

I would iterate thru that, (actually a similar) Array, Use Dir to find any and all matching files in NewFiles, compare each to the files AccessFiles and append or move as required.

This is not complete Code, since I don't do Access and OBP is doing an excellent job for you.

Code:

---

Sub Main()
'Iterates thru NewFiles and Checks AccessFiles, decides which sub Procedure to pass FileName to

NewPAth = 'Folder Picker for AccessFiles Folder

StringList = Array("MAIN", "CASE", "PRIMARY", "SECONDARY", "ALTERNATE")
For i = LBound(StringList) to UBound(StringList)
'StatusBar Code

FileName = FixedPath & "*" & StringList(i)  & "*"
If Len(FileName) > 1 Then
ExistingFile = CheckFile(NewPath, StringList(i)) 
If Len(ExistingFile) > 0 Then
AppendFile FileName, NewPath, ExistingFile
Else
MoveFile NewPath, FileName
End If
End If
Next i
End Sub

---

Code:

---

Function CheckFile(NewPAth As String, InnerString As String) As String
'StatusBar Code
CheckFile = Dir NewPAth  & "*" & InnerString  & "*"
End Function

---

Code:

---

Sub AppendFile(FileName As String, NewPath As String, ExistingFile As String)
'StatusBar Code
'Code to open FixedPath & FileName for Append
'Code to Open and append to NewPath & ExistingFile
End Sub

---

Code:

---

Sub MoveFile(FileName as String, NewPath As String)
'StatusBar Code
'Code to move FixedPath & FileName to NewPath
End Sub

---

VBA can indeed open and write to a file extension dat. With that, I have removed the strFileTemp and create text file portion. However, FileFound is where the issue. The existing file will have characters before and after the Checkfile(i) array string. For instance, 123456TESTMAINabcd or charTESTPRIMARY789. That seems to be the issue. When I reduced the filename in the directory to TESTMAIN, it ran through smoothly.

I tried revising FileFound = Dir(sDest & CheckFile(i) & "*.*") several different ways. Just so I am communication correctly the string TESTMAIN TESTPRIMARY and so on are apart of the filename. There will be character before and after the Checkfile string.

FYI, SamT thanks for the contribution. This method seems to be working for OBP so hopefully after this last issue is ironed out I will have it running as well. If not, your suggestion may have to be something I visit.

Andy, the problem is with how you ask Dir to find your files.
I have changed this line

FileFound = Dir(sDest & CheckFile(i) & "*.*")

to

FileFound = Dir(sDest & "*" & CheckFile(i) & "*.*")

and it now finds
123456TESTMAINabcd
however it finds the first version of TestMain, opens it and then appends the data to it.
So we are now back to how does the VBA code know which is the correct TESTMAIN for it to append to.
I can change it to iterate through all of them and append the data to them all, but that is hardly efficient.
Which is why I asked you if you know the names of the Master files why aren't those names in a table so that the VBA can know the correct table to append to, you would not even need to find it.

SamT, this is more like BASIC and VBA than Access, as Access objects are not really involved, so I am going back 30+ years for the correct code etc.

Right, this version of the code checks for the files in the list (which are in TestFolder2) checks if they are in the array, if it is not in the array it copies it to TestFolder where the masters are.
If the file from the list is in the array it appends the data to all the Master files with that file name in the array.
It works and currently updates 4 versions of TestMain, 2 versions of TESTCASE and copies one file called testfile text, it ells you which file is being appended to and from which new file.

Here is the code in full

Code:

---

Dim CheckFile, FileFound As String, i As Integer, datastring As String
Dim sDest As String, firstfile As String, filcopied As Integer
Dim ctlList As Control, varItem As Integer, posn As Integer, FileName As String, result As Integer, fileinarray As Integer
CheckFile = Array("TESTMAIN", "TESTCASE", "TESTPRIMARY", "TESTSECONDARY", "TESTALTERNATE")
 On Error GoTo errorcatch
 'The following lines of code use a network path for the source file :
sDest = "C:\Users\A C\Downloads\TestFolder\" ' & CheckFile(0) ' will be changed to CurrentProject.path & MAXIMOExports
Set ctlList = Me.List0
For varItem = 0 To ctlList.ListCount - 1
    For x = 1 To Len(ctlList.ItemData(varItem))
        ' Parse filename only
        If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
    Next x
    firstfile = ctlList.ItemData(varItem)
    FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
    'MsgBox varItem & " - " & FileName
    result = 0
    fileinarray = 0
    filecopied = 0
    For i = LBound(CheckFile) To UBound(CheckFile)
        result = InStr(1, ctlList.ItemData(varItem), CheckFile(i))
        'MsgBox ctlList.ItemData(varItem) & " - " & CheckFile(i)
        If result <> 0 Then
            MsgBox result & " - File is in array append it"
            fileinarray = 1
            FileFound = Dir(sDest & "*" & CheckFile(i) & "*.*")
            GoSub Appendsub
            'Open sSourceFile For Input As SourceFileNum
            Do Until FileFound = ""  ' Start the loop.
                ctr = ctr + 1
                FileFound = Dir()  ' Getting next entry.
                If FileFound <> "" Then GoSub Appendsub
            Loop
            Exit For
        End If
    Next i
    If fileinarray = 0 Then
        If filecopied = 0 Then
            MsgBox result & " - file is not in array copy it"
            FileCopy ctlList.ItemData(varItem), sDest & FileName
            filecopied = 1
        End If
    End If
Next varItem

Exit Sub
errorcatch:
Close #2
Close #1
MsgBox "Error - " & Err.Description

           
Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
    Line Input #1, datastring
    Print #2, datastring
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

---

It seems like by adding the asterik to the FileFound line corrected the issue when there is a master file in the directory. For files in the listbox that do not have a master file in the sDest directory it is not performing the intial FileCopy. It goes to error catch - Path not found. Same goes for the newer code you posted. But it is appending if there is master already in sDest. Just not doing the initial copy if there is not a master file there. I apologize. You've been great with your help and I'm sorry its working this way.

I hear what you are saying about how does it know which TESTMAIN to append to. There will only be one TESTMAIN, TESTPRIMARY, and so forth in sDest directory. If there is a second file of that type, append to the master. If there is a third of that type, append to the master.

Note that my version copies the non array file from TestFolder2 to TestFolder, so you need to ensure that your path or paths reflect that in the line of code

FileCopy ctlList.ItemData(varItem), sDest & FileName

my ctlList.ItemData(varItem) contains the complete path to the list file, does yours?

Ok, no it is not. It is the filename only. So ctlList.ItemData(varItem) should be set to the full path file?

Yes this is what my non array entry looks like

C:\Users\A C\Downloads\TestFolder2\testfile.txt

Quote:

---

For files in the listbox that do not have a master file in the sDest directory it is not performing the intial FileCopy

---

Take a look at the use of ExistingFile and Checkfile in my last post

What is testfile.txt? I thought we were taking the files from the listbox and using Checkfile to test if the file from the listbox contained one of the strings in the array Checkfile(i). To fix, what does ctlList.Item(varItem) need to point to. I have it pointing to the filename in the listbox.

I do apologize for not putting this all together. You've been very helpful and I hate to ask questions like this.

I have a root folder listbox on the form. I set ctlList.ItemData(varItem) to that, which is the complete file path. Didn't seem to do anything.

Andy, without your files I have had to produce dummy data for test purposes and testfile.txt is just a file that I already had that I new did not fit any of your Array names.
So I put that in the list to test the code and it worked fine.
Without access to your computer I can only simulate what I think you need.
If you can provide the actual paths to your files and a non array name I can reproduce that on my computer.

Don't worry about how long this is taking, on the other Forum I "Work on" my motto is "I do not give easily" and I don't.

It is much easier being able to converse with you in real time, rather that very late at night, you must have got up pretty early this morning.

Yes, real time conversation is much more effective. I do appreciate your patience. You don't need the actual files just the complete file name?

Also, let's say its the first time the program is opened and a user will copy files. That would mean there are not any files in sDest. So I think that the problem when copying the files for the first time. The files in the list will contain the Array strings. But if this is the frist time files are being copied, sDest will be empty. It seems that when sDest is empty Checkfile(i) thinks that TESTMAIN is in the directory when really its the first time TESTMAIN is being copied. So it tries to append and when it gets to FileFound it stops because there is not a file in sDest to append to. I may have explained that horribly so just say so.

I had not considered "first use" as I assumed it was already a mature system.
But we can build some checks in to prevent that.
No I don't really need actual files, just Paths to where your data is stored, I will then duplicate that as near as possible to test the code.

Ok, I can provide that. I think its the first use situation that is error source. If the built in checks do not resolve I can provide the paths of data storage.

Here is the new code that allows for the Master not being in the Folder already.

Code:

---

Dim CheckFile, FileFound As String, i As Integer, datastring As String
Dim sDest As String, firstfile As String, filcopied As Integer
Dim ctlList As Control, varItem As Integer, posn As Integer, FileName As String, result As Integer, fileinarray As Integer
CheckFile = Array("TESTMAIN", "TESTCASE", "TESTPRIMARY", "TESTSECONDARY", "TESTALTERNATE")
 On Error GoTo errorcatch
 'The following lines of code use a network path for the source file :
sDest = "C:\Users\A C\Downloads\TestFolder\" ' & CheckFile(0) ' will be changed to CurrentProject.path & MAXIMOExports
Set ctlList = Me.List0
For varItem = 0 To ctlList.ListCount - 1
    For x = 1 To Len(ctlList.ItemData(varItem))
        ' Parse filename only
        If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
    Next x
    firstfile = ctlList.ItemData(varItem)
    FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
    'MsgBox varItem & " - " & FileName
    result = 0
    fileinarray = 0
    filecopied = 0
    For i = LBound(CheckFile) To UBound(CheckFile)
        result = InStr(1, ctlList.ItemData(varItem), CheckFile(i))
        'MsgBox ctlList.ItemData(varItem) & " - " & CheckFile(i)
        If result <> 0 Then
            MsgBox result & " - File is in array append it"
            fileinarray = 1
            FileFound = Dir(sDest & "*" & CheckFile(i) & "*.*")
            If FileFound = "" Then
                fileinarray = 0
                Exit For
            End If
            GoSub Appendsub
            'Open sSourceFile For Input As SourceFileNum
            Do Until FileFound = ""  ' Start the loop.
                ctr = ctr + 1
                FileFound = Dir()  ' Getting next entry.
                If FileFound <> "" Then GoSub Appendsub
            Loop
            Exit For
        End If
    Next i
    If fileinarray = 0 Then
        If filecopied = 0 Then
            MsgBox result & " - file is not in array copy it"
            FileCopy ctlList.ItemData(varItem), sDest & FileName
            filecopied = 1
        End If
    End If
Next varItem

Exit Sub
errorcatch:
Close #2
Close #1
MsgBox "Error - " & Err.Description

           
Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
    Line Input #1, datastring
    Print #2, datastring
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return

---

It is this part that does the check

If FileFound = "" Then
fileinarray = 0
Exit For
End If

From a quick check, that seems have worked the way I was hoping. I'll do some more testing to verify but from a fast check it looks great. I am going to need to insert a few commas between the last field of the master file and where the new file appended data starts. I've looked online but can't find anything that shows me how to do that. Adding this hopefully gets this part finished.

This line of code in the subroutine
Print #2, datastring

print the data to the file

So you could use
datastring = ",,,,,"
and
Print #2, datastring
to put it in the file before going through the #1 file.
So it would become

Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
datastring = ",,,,,"
Print #2, datastring
Do Until EOF(1)
Line Input #1, datastring
Print #2, datastring
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return