Check If File Exist - Append To If True

[mr.donaldson]

I am hoping to get some help to get me through this issue. I am trying to copy files from a listbox to a directory. I need to check if a file exist and if it does, append to the existing file. The filenames are similar in that the source file is "TESTMAIN60" and the destination file is "TESTMAIN" I set an array with the filename strings and am trying to loop through the directory to check for the existing file. If the source filename matches a destination filename, I know I should open the source for reading and open the destination file for appending. I need help incorporating everything. Below is the code I have now, but even the check file portion is not running correctly. I receive compile error: Next without for. The first portion of the code replaces the file extension as .txt. If I am going about this the wrong way, please direct me. Any help is appreciated.

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

 

[ashleedawg]

The Next Without For error is because the If statement If FileFound = "" Then is missing it's End If.

VBA confuses it's Next's and End's and Loop's sometimes when trying to convey errors like this...

 

[Cronk]

No, that If statement has the endif 3 lines down.

It's the If Not AllOk then that is the problem.

Not indenting code strikes again.

 

[mr.donaldson]

You are both correct. I'm getting the files that do not exist as opposed to the files that do exist that share a common filename.

 

[Cronk]

If you care to post your code properly indented, more people including me, would be interested in looking at it.

 

[mr.donaldson]

Hope this easier to follow.

Private Sub cmdCopyFiles_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\" ' 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
                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

 

[Cronk]

A bit easier to follow, sort of.

There is nothing before this line

If Not AllOk Then

setting the variable to true.

NB the default value for a boolean variable is false.

 

[MarkK]

Here's properly indented...

Private Sub cmdCopyFiles_Click()
    Dim CheckFile
    Dim FileFound As String
    Dim AllOk As Boolean
    Dim i As Integer
    Dim ErrMsg As String
    Dim sDest As String
    Dim strFileTemp As String
    Dim FileExist As String
    Dim ctlList As Control
    Dim varItem As Variant
    Dim posn As Integer
    Dim 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
    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

This code is too dense. I would use subroutines. Also, I would not do this...

    ' Return Control object variable pointing to Me.FileList list box.
    Set ctlList = Me.FileList

...and then use the variable as a stand-in for the list. Just use the list itself. Doing it this way obscures the target of the operation more than clarifies.

To enumerate thru selected items use the ItemsSelected property of the list.

Your variable AllOK is never set to true anywhere. What is it for? If it is never True, then you can probably remove it from the routine.

hth
Mark