Consulting

Page 4 of 9 FirstFirst ... 2 3 4 5 6 ... LastLast
Results 61 to 80 of 162

Thread: File Dialog-Browse/Save/Append

  1. #61
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    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.

  2. #62
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    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.

  3. #63
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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.
    Last edited by OBP; 07-24-2017 at 01:18 AM.

  4. #64
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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.

  5. #65
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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

  6. #66
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    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.

  7. #67
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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?

  8. #68
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Ok, no it is not. It is the filename only. So ctlList.ItemData(varItem) should be set to the full path file?

  9. #69
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    Yes this is what my non array entry looks like

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

  10. #70
    VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,709
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #71
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    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.

  12. #72
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    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.

  13. #73
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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.

  14. #74
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    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?

  15. #75
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    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.

  16. #76
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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.

  17. #77
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    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.

  18. #78
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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

  19. #79
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    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.

  20. #80
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •