LucienJarrett
Technical User
Greetings,
I was asked by my boss to see what i can do to automate the import of purchase orders using macros. The data comes from a third party application and has been formatted for import into ACCPAC. I have been able to import successfully via ODBC. What i want to do now is to be able to do it successfully via macros.
See below what i have managed to do so far. When i run this nothing seems to happen and i don't see where in the code it tries to connect to the data tables.
Dim Session As New AccpacCOMAPI.AccpacSession
Dim UserName As String
Dim Password As String
Dim Database As String
UserName = "ADMIN"
Password = "ADMIN"
Database = "GASDB"
'Initialise Session
Set Session = AccpacCOMAPI.AccpacSession
Session.Init "", "XY", "XY1100", "56A"
Session.Open UserName, Password, Database, Date, 0, ""
'Views
Dim POPOR1header As AccpacCOMAPI.AccpacView
Dim POPOR1detail1 As AccpacCOMAPI.AccpacView
Dim POPOR1detail1Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1headerFields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail2 As AccpacCOMAPI.AccpacView
Dim POPOR1detail2Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail3 As AccpacCOMAPI.AccpacView
Dim POPOR1detail3Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail4 As AccpacCOMAPI.AccpacView
Dim POPOR1detail4Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail5 As AccpacCOMAPI.AccpacView
Dim POPOR1detail5Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail6 As AccpacCOMAPI.AccpacView
Dim POPOR1detail6Fields As AccpacCOMAPI.AccpacViewFields
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)
mDBLinkCmpRW.OpenView "PO0620", POPOR1header
mDBLinkCmpRW.OpenView "PO0630", POPOR1detail1
mDBLinkCmpRW.OpenView "PO0610", POPOR1detail2
mDBLinkCmpRW.OpenView "PO0632", POPOR1detail3
mDBLinkCmpRW.OpenView "PO0619", POPOR1detail4
mDBLinkCmpRW.OpenView "PO0623", POPOR1detail5
mDBLinkCmpRW.OpenView "PO0633", POPOR1detail6
Set POPOR1headerFields = POPOR1header.Fields
Set POPOR1detail1Fields = POPOR1detail1.Fields
Set POPOR1detail2Fields = POPOR1detail2.Fields
Set POPOR1detail3Fields = POPOR1detail3.Fields
Set POPOR1detail4Fields = POPOR1detail4.Fields
Set POPOR1detail5Fields = POPOR1detail5.Fields
Set POPOR1detail6Fields = POPOR1detail6.Fields
POPOR1header.Compose Array(POPOR1detail2, POPOR1detail1, POPOR1detail3, POPOR1detail4, POPOR1detail5)
POPOR1detail1.Compose Array(POPOR1header, POPOR1detail2, POPOR1detail4, Nothing, Nothing, POPOR1detail6)
POPOR1detail2.Compose Array(POPOR1header, POPOR1detail1)
POPOR1detail3.Compose Array(POPOR1header, POPOR1detail4)
POPOR1detail4.Compose Array(POPOR1header, POPOR1detail2, POPOR1detail1, POPOR1detail3)
POPOR1detail5.Compose Array(POPOR1header)
POPOR1detail6.Compose Array(POPOR1detail1)
POPOR1header.Order = 1
POPOR1header.Order = 0
POPOR1headerFields("PORHSEQ").PutWithoutVerification ("0") ' Purchase Order Sequence Key
POPOR1header.Init
POPOR1header.Order = 1
'temp = POPOR1detail1.Exists
POPOR1detail1.RecordClear
POPOR1detail3.Init
POPOR1detail2.Init
'temp = POPOR1header.Exists
Exit Sub
ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long
If Errors Is Nothing Then
MsgBox Err.Description
Else
lCount = Errors.Count
If lCount = 0 Then
MsgBox Err.Description
Else
For lIndex = 0 To lCount - 1
MsgBox Errors.Item(lIndex)
Next
Errors.Clear
End If
Resume Next
End If
End Sub
Thank you in advance for any assistance.
I was asked by my boss to see what i can do to automate the import of purchase orders using macros. The data comes from a third party application and has been formatted for import into ACCPAC. I have been able to import successfully via ODBC. What i want to do now is to be able to do it successfully via macros.
See below what i have managed to do so far. When i run this nothing seems to happen and i don't see where in the code it tries to connect to the data tables.
Dim Session As New AccpacCOMAPI.AccpacSession
Dim UserName As String
Dim Password As String
Dim Database As String
UserName = "ADMIN"
Password = "ADMIN"
Database = "GASDB"
'Initialise Session
Set Session = AccpacCOMAPI.AccpacSession
Session.Init "", "XY", "XY1100", "56A"
Session.Open UserName, Password, Database, Date, 0, ""
'Views
Dim POPOR1header As AccpacCOMAPI.AccpacView
Dim POPOR1detail1 As AccpacCOMAPI.AccpacView
Dim POPOR1detail1Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1headerFields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail2 As AccpacCOMAPI.AccpacView
Dim POPOR1detail2Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail3 As AccpacCOMAPI.AccpacView
Dim POPOR1detail3Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail4 As AccpacCOMAPI.AccpacView
Dim POPOR1detail4Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail5 As AccpacCOMAPI.AccpacView
Dim POPOR1detail5Fields As AccpacCOMAPI.AccpacViewFields
Dim POPOR1detail6 As AccpacCOMAPI.AccpacView
Dim POPOR1detail6Fields As AccpacCOMAPI.AccpacViewFields
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)
mDBLinkCmpRW.OpenView "PO0620", POPOR1header
mDBLinkCmpRW.OpenView "PO0630", POPOR1detail1
mDBLinkCmpRW.OpenView "PO0610", POPOR1detail2
mDBLinkCmpRW.OpenView "PO0632", POPOR1detail3
mDBLinkCmpRW.OpenView "PO0619", POPOR1detail4
mDBLinkCmpRW.OpenView "PO0623", POPOR1detail5
mDBLinkCmpRW.OpenView "PO0633", POPOR1detail6
Set POPOR1headerFields = POPOR1header.Fields
Set POPOR1detail1Fields = POPOR1detail1.Fields
Set POPOR1detail2Fields = POPOR1detail2.Fields
Set POPOR1detail3Fields = POPOR1detail3.Fields
Set POPOR1detail4Fields = POPOR1detail4.Fields
Set POPOR1detail5Fields = POPOR1detail5.Fields
Set POPOR1detail6Fields = POPOR1detail6.Fields
POPOR1header.Compose Array(POPOR1detail2, POPOR1detail1, POPOR1detail3, POPOR1detail4, POPOR1detail5)
POPOR1detail1.Compose Array(POPOR1header, POPOR1detail2, POPOR1detail4, Nothing, Nothing, POPOR1detail6)
POPOR1detail2.Compose Array(POPOR1header, POPOR1detail1)
POPOR1detail3.Compose Array(POPOR1header, POPOR1detail4)
POPOR1detail4.Compose Array(POPOR1header, POPOR1detail2, POPOR1detail1, POPOR1detail3)
POPOR1detail5.Compose Array(POPOR1header)
POPOR1detail6.Compose Array(POPOR1detail1)
POPOR1header.Order = 1
POPOR1header.Order = 0
POPOR1headerFields("PORHSEQ").PutWithoutVerification ("0") ' Purchase Order Sequence Key
POPOR1header.Init
POPOR1header.Order = 1
'temp = POPOR1detail1.Exists
POPOR1detail1.RecordClear
POPOR1detail3.Init
POPOR1detail2.Init
'temp = POPOR1header.Exists
Exit Sub
ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long
If Errors Is Nothing Then
MsgBox Err.Description
Else
lCount = Errors.Count
If lCount = 0 Then
MsgBox Err.Description
Else
For lIndex = 0 To lCount - 1
MsgBox Errors.Item(lIndex)
Next
Errors.Clear
End If
Resume Next
End If
End Sub
Thank you in advance for any assistance.