Consulting

Page 2 of 9 FirstFirst 1 2 3 4 ... LastLast
Results 21 to 40 of 162

Thread: File Dialog-Browse/Save/Append

  1. #21
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I don't really think the append portion is something I need to do. I think the thinking was if there was more than 1 of the original exported files to combine them and have one master file for that file set. But if a file has already been imported into Access and you later have an additional file you need to append to the table in Access. If you append to the file and then try to import, it's not going to play nice because it will see duplicates. I should be able to set up an append to table update feature that would work for every additional file that's ever required. At least that's what I think. You have any thoughts and past experiences with this?

  2. #22
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Access should be the main file obviously. Yes are correct about Access not liking the duplicates situation.
    Believe it or not I haven't done much importing of data in Access for myself, so I can't really comment that much, what I would suggest is that the original files get renamed to indicate that they have been imported and can therefore be ignored in future.

  3. #23
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    That's probably the best way to go. I don't see a way to append to the file if it's already been imported as a table. There's probably a routine I could right to ignore the data already in the table and import what's new but that isn't worth it if access already has an append feature. Just out of curiosity have you ever experienced the file copy command not copying the file correctly? What I'm asking is after copying a file should you compare the selected and copied file to look for errors? I've never known this to happen even if you are changing the file extension but thought I would ask?

  4. #24
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    As I said I haven't done much with text files, but you certainly could write a routine to compare them if you are really worried about the data integrity, you can also get free software that compares files.

  5. #25
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I've attempted a couple of different methods but not gotten what I am looking for. I'm trying to identify if a file exist before copying and if it does append to it. What do you think I can try differently and how to incorporate both open file for reading (source file) open file for appending (destination file). I thought an array was better off than long nested if then statements. But if there is a better way I'm open for suggestions.

    Private Sub Command111_Click()
    
    
    Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As String
    Dim sDest As String, strFileTemp As String, FileExist As String
    Dim ctlList As Control, varItem As Variant, posn As Integer, FileName As String
    CheckFile = Array("*TESTMAIN*", "*TESTCASE*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")
    
    
    'The following lines of code use a network path for the source file :
    sDest = CurrentProject.path & "\Test Folder\" s
    
    
        
        ' Return Control object variable pointing to Me.FileList list box.
        Set ctlList = Me.FileList
      
        ' Enumerate through selected items.
        For varItem = 0 To ctlList.ListCount - 1
            ' Print value of bound column - used for testing purposes.
            'MsgBox ctlList.ItemData(varItem)
            For x = 1 To Len(ctlList.ItemData(varItem))
            ' Parse filename only
            If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
            Next x
            FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
            ' Print value of bound column - used for testing purposes.
            MsgBox FileName, vbInformation, "For Testing"
            ' Replace the extension with _Temp.txt for a temp file
            strFileTemp = Replace(ctlList.ItemData(varItem), ".dat", "_Temp.txt")
            For i = LBound(CheckFile) To UBound(CheckFile)
            FileFound = Dir(MyPath & "\" & CheckFile(i))
            If FileFound = "" Then
            AllOk = False
            ErrMsg = ErrMsg & CheckFile(i) & vbNewLine
            End If
            Next i
            If Not AllOk Then
            'MsgBox strFileTemp, vbInformation, "FileExist"
            ' Copy .txt files
            FileCopy ctlList.ItemData(varItem), sDest & strFileTemp
            ' Reset file extension .dat
            FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
            ' Copy dat file
            FileCopy ctlList.ItemData(varItem), sDest & FileName
        Next varItem
        ' Print file storage location
        MsgBox CurrentProject.path & "\Test Folder\", vbInformation, "Files Have Been Copied To The Below Location:"
        
        'Kill strFileTemp
        
        ' Check that all files in Me.FileList copied MaximoExports directory
        If Dir(CurrentProject.path & "\Test Folder\") = FileName Then
        MsgBox "All files were successfully imported"
        Else
        MsgBox "All Files Did Not Import. Return To Import Menu"
        End If
        
     
    End Sub

  6. #26
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    An End If was left out causing the error. It is running; however, it provides the files that do not exist in the directory as opposed to the files that share a common filename as any of the files being copied.

  7. #27
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Updated - It's identifying the existing files in the destination source. I'm not sure if I have the sequence of steps correct. Maybe the array should be the first thing that is processed. What do I need to set Open File For Append to for the existing file? If you don't mind giving me a hint. Here's updated code. Thanks

    Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As StringDim sDest As String, strFileTemp As String, FileExist As String
    Dim ctlList As Control, varItem As Variant, posn As Integer, FileName As String
    CheckFile = Array("*LOCMAIN*", "*LOCSPEC*", "*ASSETMAIN*", "*ASSETSPEC*", "*DIGITASSET*")
    
    
    
    
    
    
    'The following lines of code use a network path for the source file :
    sDest = CurrentProject.path & "\Test Folder\" ' will be changed to CurrentProject.path & MAXIMOExports
    
    
         ' Return Control object variable pointing to Me.FileList list box.
        Set ctlList = Me.FileList
      
        ' Enumerate through selected items.
        For varItem = 0 To ctlList.ListCount - 1
            ' Print value of bound column - used for testing purposes.
            'MsgBox ctlList.ItemData(varItem)
            For x = 1 To Len(ctlList.ItemData(varItem))
            ' Parse filename only
                If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
            Next x
            FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
            ' Print value of bound column - used for testing purposes.
            MsgBox FileName, vbInformation, "For Testing"
            ' Replace the extension with _Temp.txt for a temp file
            strFileTemp = Replace(ctlList.ItemData(varItem), ".dat", "_Temp.txt")
            For i = LBound(CheckFile) To UBound(CheckFile)
                FileFound = Dir(MyPath & "\" & CheckFile(i))
                MsgBox FileFound
                    If FileFound = "" Then
                        FileExist = CheckFile(i)
                        MsgBox FileExist
                        AllOk = False
                    End If
                    If strFileTemp Like FileExist Then
                        FileExist = True
                        MsgBox FileExist
                    End If
                ErrMsg = ErrMsg & CheckFile(i) & vbNewLine
            Next i
                If Not AllOk Then
            'MsgBox strFileTemp, vbInformation, "FileExist"
            ' Copy .txt files
                    FileCopy ctlList.ItemData(varItem), sDest & strFileTemp
            ' Reset file extension .dat
                    FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
            ' Copy dat file
                    FileCopy ctlList.ItemData(varItem), sDest & FileName
                End If
        Next varItem
        ' Print file storage location
        MsgBox CurrentProject.path & "\Test Folder\", vbInformation, "Files Have Been Copied To The Below Location:"
        
        'Kill strFileTemp
        
        ' Check that all files in Me.FileList copied MaximoExports directory
        If Dir(CurrentProject.path & "\Test Folder\") = FileName Then
        MsgBox "All files were successfully imported"
        Else
        MsgBox "All Files Did Not Import. Return To Import Menu"
        End If

  8. #28
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Sorry I didn't get back to you yesterday, Tuesday is my Poker Night.
    As you have developed the code where abouts do you want the Append VBA code?

    The code is simple

    Open filename For Append As #FileNumber

    You can then use

    Print #FileNumber, Textstring

    or

    Write #FileNumber, Textstring

  9. #29
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    No worries at all. Hope you playted well.
    I think it should go after,
    If strFileTemp Like FileExist Then
    FileExist = True
    MsgBox FileExist
    This should be where if a similar file already exist for the file being copied is set True. At that point, the source file (strFileTemp) is opened for reading and the destination file (checkfile) is opened for appending. But I need to grab the destination file. Right now it is only looking for a file in a directory with a certain string.

  10. #30
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I managed to come second and win a few pounds, so not too bad, I play on Tuesdays and Sundays in local Pubs.

    I would suggest creating a test folder with copies of your files in it (unless it already is a test folder) and trying the code that I suggested.
    Have you got the code for extracting the data in to a string for sending to the file?
    I provided code for reading CSV files here

    http://www.vbaexpress.com/forum/show...ds-into-Access

    and here

    http://www.vbaexpress.com/forum/show...ividual-groups

  11. #31
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Always good to win.

    I have test files and test folder I'm working with now. I'll look at what you provided. First, I have to get the checkfile and strFileTemp to be set to the same similar files. Now in the loop, checkfile may be at TESTMAIN and strFileTemp at TEST PRIMARY. So if I opened and appended I would combine the wrong file to the destination file. That's why I said I thought my sequence of steps may be out of order. It should take the strFileTemp filename and checkfile look for existing files with that same string. If found, then open and append. Thanks for the info.

  12. #32
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Does the test you are using pick up the File's Date?
    That should tell you which is the original.
    If you use File Scripting (FSO) you can pick up data like "last accessed" and file size.

  13. #33
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    The program loops through the listbox and checks if one of the selected files already exist in the directory to which it is being copied. But it seems like it is setting the FileExist flag true if the text string has a match in the listbox and not the destination directory. It should only set FileExist true if the string is in the directory. If that part is working, I then try and open the selected file and matching file in the directory for appending. It will not run past that point because checkfile(i) is an array and not the actual file. Do I need to go back and use MID to retrieve the entire filename so I can actually open the file for appending? I've tried several methods already. Maybe my way of doing this is the wrong way to go about it. Maybe there is a better way to search for existing files and then proceed to the append routine. Any help or advice is welcome. Thanks

    Dim CheckFile, FileFound As String, AllOk As Boolean, i As Integer, ErrMsg As String
    Dim sDest As String, strFileTemp As String, FileExist As String
    Dim ctlList As Control, varItem As Variant, posn As Integer, FileName As String
    CheckFile = Array("*TESTMAIN*", "*TESTCASE*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")
    
    
    
    
    
    
    'The following lines of code use a network path for the source file :
    sDest = CurrentProject.path & "\Test Folder\" ' will be changed to CurrentProject.path & MAXIMOExports
    
    
         ' Return Control object variable pointing to Me.FileList list box.
        Set ctlList = Me.FileList
      
        ' Enumerate through selected items.
        For varItem = 0 To ctlList.ListCount - 1
            ' Print value of bound column - used for testing purposes.
            'MsgBox ctlList.ItemData(varItem)
            For x = 1 To Len(ctlList.ItemData(varItem))
            ' Parse filename only
                If Mid(ctlList.ItemData(varItem), x, 1) = "\" Then posn = x
            Next x
            FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
            ' Print value of bound column - used for testing purposes.
            MsgBox FileName, vbInformation, "For Testing"
            ' Replace the extension with _Temp.txt for a temp file
            strFileTemp = Replace(ctlList.ItemData(varItem), ".dat", "_Temp.txt")
            
            AllOk = True
            For i = LBound(CheckFile) To UBound(CheckFile)
                FileFound = Dir(MyPath & "\" & CheckFile(i))
                    If FileFound = "" Then
                        FileExist = CheckFile(i)
                        MsgBox FileExist
                        AllOk = False
                    End If
                    
                    If strFileTemp Like FileExist Then
                        Open strFileTemp For Input As #1
                        'Open FileExist For Output As #8
                            'Do Until EOF(1)
                            'xchar = Input(1, #1)
                            'MsgBox xchar
                            FileExist = True '- used in testing
                            MsgBox FileExist '- used in testing                                    
                    End If
    
    
                'ErrMsg = ErrMsg & CheckFile(i) & vbNewLine
            Next i
    
    
                If Not AllOk Then
           		 'MsgBox strFileTemp, vbInformation, "FileExist"
            	' Copy .txt files
                    FileCopy ctlList.ItemData(varItem), sDest & strFileTemp
           		 ' Reset file extension .dat
                    FileName = Right(ctlList.ItemData(varItem), Len(ctlList.ItemData(varItem)) - posn)
           		 ' Copy dat file
                    FileCopy ctlList.ItemData(varItem), sDest & FileName
                End If
        Next varItem
    
    
        ' Print file storage location
        MsgBox CurrentProject.path & "\Test Folder\", vbInformation, "Files Have Been Copied To The Below Location:"
        
        'Kill strFileTemp
        
        ' Check that all files in Me.FileList copied MaximoExports directory
        If Dir(CurrentProject.path & "\Test Folder\") = FileName Then
        MsgBox "All files were successfully imported"
        Else
        MsgBox "All Files Did Not Import. Return To Import Menu"
        End If

  14. #34
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I will take a look at the code and see if I can tell where it is going wrong.
    Last edited by OBP; 07-18-2017 at 02:21 AM.

  15. #35
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    I don't mind if the method is different. If the end result is the same, then its ok with me.

  16. #36
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Can you explain your logic of having a list and an array, what is the purpose of CheckFile = Array("*TESTMAIN*", "*TESTCASE*", "*TESTPRIMARY*", "*TESTSECONDARY*", "*TESTALTERNATE*")?
    I have created a form with a list and I am now testing the code, but I will be busy this afternoon with the grandchildren and then Poker tonight, so I probably won't get around to really testing the code until tomorrow.

  17. #37
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    TESTMAIN, TESTCASE, TESTPRIMARY, TESTSECONDARY, TESTALTERNATE will always be in the filename. There may be leading or trailing numbers or a combination of both the filename. TESTMAIN070117 may already exist in the directory and the file being copied (file in the listbox) is TESTMAIN072317. TESTMAIN072317 (file in listbox) needs to be added/merged with the file that already exist in the directory (TESTMAIN070117). CheckFile is supposed to search for such files in the destination folder. If the file in listbox is TESTMAIN0702317, when Checkfile runs it should look for an existing TESTMAIN file in the directory. If there is an existing file, at that point the selected file should be open for reading and the destination file should be open for appending. If not, just a plain FileCopy will be performed.

    Good luck in poker. Thanks for looking at this.

  18. #38
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Before I go out, I see what you are doing with the Array, so what is the List box for?
    Is that where you put the File names that the serach finds?

  19. #39
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    No, the files selected from the file dialog are placed in the listbox. I put them in the listbox as a way the use can see all that was selected. As the copy procedure takes place, check if a common file in the destination directory exist. If file exist, merge to existing. If not, filecopy.

  20. #40
    VBAX Regular
    Joined
    May 2017
    Posts
    98
    Location
    Hope that clarifies. Listbox is a way to store files selected. Probably not required but wanted users to see files selected if several files were selected. User will have a chance to delete a file if one was selected out of error before copying.

Posting Permissions

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