VBA example - Handling Import and Export Files

Because sometimes you have to use VBA. This example was for a 3rd party's interface.

Option Compare Database

Public Sub ifcHR185GL_Actuals()
    
    DoCmd.SetWarnings False
    stAurion = CheckEnviron()             ' Get & set Env
    
    locIn = "C:\Temp\"
    locOut = "C:\Temp\"
    
    stFilePathIn = locIn & "gl_actuals.txt"
    stFilePathOutHdr = locOut & "JACT" & Format(Date, "YYMMDD") & "_H.txt"
    stFilePathOutLn = locOut & "JACT" & Format(Date, "YYMMDD") & "_L.txt"
    stFilePathOut = locOut & "JACT" & Format(Date, "YYMMDD") & ".txt"
    
'    stFilePathIn = "U:\INTERFACE\CURRENT\IN\gl_interface_"
'    stFilePathOut = "U:\INTERFACE\CURRENT\OUT\JACT" & Format(Date, "YYMMDD") & ".txt"
    
    'Delete the existing records
    DoCmd.OpenQuery "qryHR185Actuals_Aurion_Del", acNormal, acEdit
    DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_Del", , acEdit
    
    'Import the Aurion text file and create a new table
    DoCmd.TransferText acImportDelim, "HR185Gl_actuals Import Specification", "tblHR185Actuals_Aurion", _
    stFilePathIn, False, ""
        
    'Update header record with system date and PPEDN date to be used for journal lines
    'may be commented out when the file has a PPEND
    'but it requires import spec changes and qry_App changes
    DoCmd.OpenQuery "qryHR185HeaderRecord_Upd", acNormal, acEdit
    
    'Create output table
    ''DoCmd.OpenQuery "qryHR185Journal_Header_Actuals_App", acNormal, acEdit
    DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_App", acNormal, acEdit
    DoCmd.OpenQuery "qryHR185Journal_Line_Actuals_UpdPPEND", acNormal, acEdit

    ' Delete old copy of output files if found
    stScratch = Dir(stFilePathOutHdr)
    If stScratch <> "" Then
     Kill stFilePathOutHdr
    End If
    stScratch = Dir(stFilePathOutLn)
    If stScratch <> "" Then
     Kill stFilePathOutLn
    End If
    stScratch = Dir(stFilePathOut)
    If stScratch <> "" Then
     Kill stFilePathOut
    End If
    
    'Export the Header and Line tables
    DoCmd.TransferText acExportFixed, "tblHR185Journal_Header_Actuals Export Specification", _
    "tblHR185Journal_Header_Actuals", stFilePathOutHdr
    DoCmd.TransferText acExportFixed, "tblHR185Journal_Line_Actuals Export Specification", _
    "qryHR185Journal_Line_Actuals_Export", stFilePathOutLn
            
    ' Merge Header and Line files into final export file.
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fStrmH = fso.OpenTextFile(stFilePathOutHdr)
    Set fStrmL = fso.OpenTextFile(stFilePathOutLn)
    Set fStrmM = fso.CreateTextFile(stFilePathOut, True)
    Do Until fStrmH.AtEndOfStream
        strLine = fStrmH.ReadLine
        fStrmM.writeline strLine
    Loop
    Do Until fStrmL.AtEndOfStream
        strLine = fStrmL.ReadLine
        fStrmM.writeline strLine
    Loop
    
    fStrmH.Close
    fStrmL.Close
    fStrmM.Close
    
    ' Cleanup Header and Line files.
    Kill stFilePathOutHdr
    Kill stFilePathOutLn
    DoCmd.SetWarnings True
    
    '!!!   COMMENT OUT THE LINE BELOW LATER
    MsgBox "completed"
    
End Sub