Automate Daily Import

[Pat Hartman]

Here's an edited section of the code. My code does a lot of error checking which is not relevant to your process but I left some of it because you might need it. The first procedure verifies the existence of some folders. this code is run from a form so the source and target folder names are on the form. In your case, you should set up defaults in a table and get the two names from the table.

Private Sub Import()

    Dim ErrReturn As Long
    
On Error GoTo Err_Proc

    
    If Me.txtSourceFolderName & "" = "" Then
        MsgBox "Please enter a source folder name.", vbOKOnly + vbCritical
        Exit Sub
    End If
    If Me.txtTargetFolderName & "" = "" Then
        MsgBox "Please enter a target folder name.", vbOKOnly + vbCritical
        Exit Sub
    End If
    If Me.cboDocID & "" = "" Then
        If IsNull(DLookup("ACKDocReceipt", "tblAuditParms", "AuditParmsID = " & Forms!frmLogin!cboAuditParmsID)) Then
        Else
            MsgBox "Please select an acknowledgement letter.", vbOKOnly + vbCritical
            Exit Sub
        End If
    End If
    
    gHoldMemberDateTime = Now()
    Me.txtDocCount = Null
    Me.txtErrCount = Null
    Me.txtErrFolder = Null
    Me.txtImportedCount = Null
    Me.txtClientIDErr = Null
    Me.txtDupsErr = Null
    Me.txtEmpIDErr = Null
    Me.txtAuditAbbrErr = Null
    Me.txtPrinted = Null
    Me.txtDocCount = LogScannedDocs(Me.txtSourceFolderName, Me.txtTargetFolderName, Me)
    Me.txtImportedCount = Me.txtDocCount - Me.txtErrCount
    
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 2501
            Resume Exit_Proc
        Case 3021
            Resume Next
'        Case 58     'file already exists
'            Resume Next
'        Case 53
'            Resume Next
        Case 91
            Resume Next
        Case 3059
            MsgBox "Import cancelled.", vbOKOnly + vbInformation
            Resume Exit_Proc
        Case 3265
            ErrReturn = 3265
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical
            Resume Exit_Proc
    End Select
End Sub

The next part also contains code you wont' need but I didn't want to cut all of it out lest I break the logic so hopefully you can work around it. My app sent acknowledgement emails or letters if the client requested them so that is part of this process. I put note lines around the salient loop that reads the files in the import folder. The app is used by service bureaus that process data for multiple clients so the file names have specific formatting requirements so that the appropriate client can be identified. The code creates detail folders below the target folder if the ones for a particular client have not been created yet. It then copies the file to the target folder and logs the file name in the documents table with the path to the file. The last step "kills" all the files in the input folder.

Private Sub Import()

    Dim ErrReturn As Long
    
On Error GoTo Err_Proc

    
    If Me.txtSourceFolderName & "" = "" Then
        MsgBox "Please enter a source folder name.", vbOKOnly + vbCritical
        Exit Sub
    End If
    If Me.txtTargetFolderName & "" = "" Then
        MsgBox "Please enter a target folder name.", vbOKOnly + vbCritical
        Exit Sub
    End If
    If Me.cboDocID & "" = "" Then
        If IsNull(DLookup("ACKDocReceipt", "tblAuditParms", "AuditParmsID = " & Forms!frmLogin!cboAuditParmsID)) Then
        Else
            MsgBox "Please select an acknowledgement letter.", vbOKOnly + vbCritical
            Exit Sub
        End If
    End If
    
    gHoldMemberDateTime = Now()
    Me.txtDocCount = Null
    Me.txtErrCount = Null
    Me.txtErrFolder = Null
    Me.txtImportedCount = Null
    Me.txtClientIDErr = Null
    Me.txtDupsErr = Null
    Me.txtEmpIDErr = Null
    Me.txtAuditAbbrErr = Null
    Me.txtPrinted = Null
    Me.txtDocCount = LogScannedDocs(Me.txtSourceFolderName, Me.txtTargetFolderName, Me)
    Me.txtImportedCount = Me.txtDocCount - Me.txtErrCount
    
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 2501
            Resume Exit_Proc
        Case 3021
            Resume Next
'        Case 58     'file already exists
'            Resume Next
'        Case 53
'            Resume Next
        Case 91
            Resume Next
        Case 3059
            MsgBox "Import cancelled.", vbOKOnly + vbInformation
            Resume Exit_Proc
        Case 3265
            ErrReturn = 3265
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical
            Resume Exit_Proc
    End Select
End Sub

 

[tmyers]

Thank you for your reply Pat, your responses are always very helpful. I am looking over your stuff now.

 

[tmyers]

I just wanted to report back saying that I have it working now and wanted to thank all of you for your input.
It imports the file, creates a log that it was successful then moves the file to another location for me to verify that it was imported if needed. All of this is started via the task scheduler.

Edit:
I also wanted to add that this was a fresh DB that only has the main table, a temp table and a few queries plus a macro. There is no user interface whatsoever as this is purely for data storage for Power BI to attach to and visualize.

 

[Pat Hartman]

Glad you got it working.