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

0 comments: