Dim sTemplateFileName As String
Dim sScriptFileName As String
sTemplateFileName = "A:\Import Script\ARImportScript.xml"
'A:\Import Script\ARImportScript.xml
'<?xml version="1.0" encoding="utf-8"?>
'<AccpacImpExp Action="16" ProgramName="AR2100" Type="IMPORT" Version="532" HasTitleRecord="1">
' <VIEW ID="AR0031" Type="4" Action="0">
' <TABLE>Invoice_Batches</TABLE>
' </VIEW>
' <VIEW ID="AR0032" Type="3" Action="0" Parent="AR0031" SelectedIDs="1 2 4 8 11 14 18 22 23 25 46 142 146 ">
' <TABLE>Invoices</TABLE>
' </VIEW>
' <VIEW ID="AR0033" Type="9" Action="0" Parent="AR0032" SelectedIDs="1 2 3 6 7 18 28 38 74 ">
' <TABLE>Invoice_Details</TABLE>
' </VIEW>
' <VIEW ID="AR0034" Type="9" Action="0" Parent="AR0032" AllFieldSelected="1">
' <TABLE>Invoice_Payment_Schedules</TABLE>
' </VIEW>
' <DBNAME>S:\airship32\Runtimes\OUTPUT\Processed Files\CTN_CCYYMMDD.j01</DBNAME>
' <DBTYPE>Single CSV File</DBTYPE>
' <BatchID>52</BatchID>
'</AccpacImpExp>
sScriptFileName = "A:\Import Script\TodayScript.xml"
'------------------------------------------------------------------------------
' Create a Script file specific to today
'------------------------------------------------------------------------------
' Load a template file saved out of accpac and change file name with a string
' replace. In this Case replacing CCYYMMDD with 20100524
'------------------------------------------------------------------------------
Dim objFSO, objFile, oTextStream
Dim sTemplateImportScript As String
' Create the FSO Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set oTextStream = objFSO.OpenTextFile(sTemplateFileName, 1)
'load the template file
sTemplateImportScript = oTextStream.ReadAll
oTextStream.Close
' Change the file name to todays date
sTemplateImportScript = Replace(sTemplateImportScript, "CCYYMMDD", CStr(Format(Now, "yyyymmdd")))
' Write the new text File
Set objFile = objFSO.OpenTextFile(sScriptFileName, 2, True)
objFile.Write (sTemplateImportScript)
objFile.Close
' Cleanup
Set oTextStream = Nothing
Set objFile = Nothing
Set objFSO = Nothing
'------------------------------------------------------------------------------
' Declare all Accpac related Vars
'------------------------------------------------------------------------------
Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Dim ARbatch As AccpacCOMAPI.AccpacView
Dim ARheader As AccpacCOMAPI.AccpacView
Dim ARdetail1 As AccpacCOMAPI.AccpacView
Dim ARdetail2 As AccpacCOMAPI.AccpacView
Dim ARdetail3 As AccpacCOMAPI.AccpacView
Dim ARdetail4 As AccpacCOMAPI.AccpacView
Dim strBatchID As String
Dim IE As AccpacImportExport.ImportExport
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE) ' Open a connection to the company database
Set IE = New AccpacImportExport.ImportExport
mDBLinkCmpRW.OpenView "AR0031", ARbatch ' The view of the batch list in Accpac
mDBLinkCmpRW.OpenView "AR0032", ARheader ' Is this the header for the batch or for once specific invoice in the batch
mDBLinkCmpRW.OpenView "AR0033", ARdetail1 ' I am not sure if these view relate to screens ina ccpac or view on the database
mDBLinkCmpRW.OpenView "AR0034", ARdetail2 '
mDBLinkCmpRW.OpenView "AR0402", ARdetail3 '
mDBLinkCmpRW.OpenView "AR0401", ARdetail4 '
' This is Creating the data structure or some sort in memory of some sort of how th batch will be structured
ARbatch.Compose Array(ARheader)
ARheader.Compose Array(ARbatch, ARdetail1, ARdetail2, ARdetail3, Nothing)
ARdetail1.Compose Array(ARheader, ARbatch, ARdetail4)
ARdetail2.Compose Array(ARheader)
ARdetail3.Compose Array(ARheader)
ARdetail4.Compose Array(ARdetail1)
ARbatch.RecordCreate 1 ' Create the actual batch
ARbatch.Fields("BTCHDESC").PutWithoutVerification "MY TEST BATCH" ' assigne the name of the batch
ARbatch.Update
' at this point I have an empty Batch.
' Get The batch Id out of the empty batch
strBatchID = ARbatch.Fields("CNTBTCH").Value
Dim str As String
With IE
.Open mDBLinkCmpRW
.ImportAction = IMPORT_INSERT
.SetViewEx "AR0031", "", VIEW_BATCH, Null, ACTION_ASK
.SetViewEx "AR0032", "AR0031", VIEW_SEQUENCED_HEADER, Null, ACTION_ASK
.SetViewEx "AR0034", "AR0032", VIEW_DETAIL_SEQUENCED, Null, ACTION_ASK
' Set The batch number to the one from the created batch
.SetBatchNumber Trim(strBatchID)
.ExecuteImportScript sScriptFileName, True
.VerifyOnPut = True
' Get The xml result report
.GetExecuteResult str
MsgBox str
.Close
End With
