File Dialog-Browse/Save/Append

[andycl]

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.

[andycl]

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.

[OBP]

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.

[OBP]

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.

[OBP]

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

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

[andycl]

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.

[OBP]

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?

[andycl]

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

[OBP]

Yes this is what my non array entry looks like

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

[SamT]

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

[andycl]

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.

[andycl]

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.

[OBP]

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.

[andycl]

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?

[andycl]

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.

[OBP]

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.

[andycl]

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.

[OBP]

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

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

[andycl]

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.

[OBP]

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