VBA to Create Shipment ACCPAC 5.6A

[Johnny48]

Hello,

I need a «Quick» way for our shipping dept to create a SHIPMENT once an order was picked....

I was thinking of this:

Dump this query onto an Excel spreadsheet:

SELECT OEORDH.ORDNUMBER, OEORDH.ORDUNIQ, OEORDD.LINETYPE, OEORDD.ITEM, OEORDD.DESC, OEORDD.QTYBACKORD
FROM OEORDD INNER JOIN OEORDH ON OEORDD.ORDUNIQ = OEORDH.ORDUNIQ
WHERE (((OEORDH.ORDNUMBER)="511768"));

Have the shipper fill in the quantities packed in the OEORDD.QTYBACKORD column...

Then use VBA code to create shipment:

Sub MainSub()
'
' Sage Accpac Macro file: C:\Documents and Settings\Shirley.GEN-LITE\Desktop\SHIPMENT2.AVB
' Recorded at: Wed Apr 20 10:55:01 2011
'

On Error GoTo ACCPACErrorHandler

' TODO: To increase efficiency, comment out any unused DB links.
Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

Dim temp As Boolean
Dim OESHI1header As AccpacCOMAPI.AccpacView
Dim OESHI1headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0692", OESHI1header
Set OESHI1headerFields = OESHI1header.Fields

Dim OESHI1detail1 As AccpacCOMAPI.AccpacView
Dim OESHI1detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0691", OESHI1detail1
Set OESHI1detail1Fields = OESHI1detail1.Fields

Dim OESHI1detail2 As AccpacCOMAPI.AccpacView
Dim OESHI1detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0745", OESHI1detail2
Set OESHI1detail2Fields = OESHI1detail2.Fields

Dim OESHI1detail3 As AccpacCOMAPI.AccpacView
Dim OESHI1detail3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0190", OESHI1detail3
Set OESHI1detail3Fields = OESHI1detail3.Fields

Dim OESHI1detail4 As AccpacCOMAPI.AccpacView
Dim OESHI1detail4Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0682", OESHI1detail4
Set OESHI1detail4Fields = OESHI1detail4.Fields

Dim OESHI1detail5 As AccpacCOMAPI.AccpacView
Dim OESHI1detail5Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0694", OESHI1detail5
Set OESHI1detail5Fields = OESHI1detail5.Fields

Dim OESHI1detail6 As AccpacCOMAPI.AccpacView
Dim OESHI1detail6Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0704", OESHI1detail6
Set OESHI1detail6Fields = OESHI1detail6.Fields

Dim OESHI1detail7 As AccpacCOMAPI.AccpacView
Dim OESHI1detail7Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0702", OESHI1detail7
Set OESHI1detail7Fields = OESHI1detail7.Fields

Dim OESHI1detail8 As AccpacCOMAPI.AccpacView
Dim OESHI1detail8Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0703", OESHI1detail8
Set OESHI1detail8Fields = OESHI1detail8.Fields

Dim OESHI1detail9 As AccpacCOMAPI.AccpacView
Dim OESHI1detail9Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0706", OESHI1detail9
Set OESHI1detail9Fields = OESHI1detail9.Fields

Dim OESHI1detail10 As AccpacCOMAPI.AccpacView
Dim OESHI1detail10Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "OE0705", OESHI1detail10
Set OESHI1detail10Fields = OESHI1detail10.Fields

OESHI1header.Compose Array(OESHI1detail1, OESHI1detail4, OESHI1detail3, OESHI1detail2, OESHI1detail5, OESHI1detail6)

OESHI1detail1.Compose Array(OESHI1header, Nothing, OESHI1detail7, OESHI1detail10, OESHI1detail8)

OESHI1detail2.Compose Array(OESHI1header)

OESHI1detail3.Compose Array(OESHI1header, OESHI1detail1)

OESHI1detail4.Compose Array(OESHI1header, OESHI1detail1)

OESHI1detail5.Compose Array(OESHI1header)

OESHI1detail6.Compose Array(OESHI1header)

OESHI1detail7.Compose Array(OESHI1detail1)

OESHI1detail8.Compose Array(OESHI1detail1, OESHI1detail9, Nothing)

OESHI1detail9.Compose Array(OESHI1detail8)

OESHI1detail10.Compose Array(OESHI1detail1)



OESHI1headerFields("DRIVENBYUI").Value = "1"                          ' Driven by UI
OESHI1header.Cancel
temp = OESHI1header.Exists
OESHI1header.Init

OESHI1headerFields("PROCESSCMD").PutWithoutVerification ("1")         ' Process OIP Command

OESHI1header.Process

OESHI1headerFields("ORDNUMBER").Value = "511770"                      ' Order Number
OESHI1headerFields("SHIP1ORDER").Value = "1"                          ' Generate Ship. from Single Order

OESHI1header.Process
OESHI1detail1Fields("LINENUM").PutWithoutVerification ("-1")          ' Line Number
OESHI1detail1.Read

OESHI1detail1Fields("QTYSHIPPED").Value = "1.0000"                    ' Quantity Shipped

OESHI1detail1.Update

OESHI1detail1Fields("LINENUM").PutWithoutVerification ("-2")          ' Line Number

OESHI1detail1.Read

OESHI1detail1Fields("QTYSHIPPED").Value = "1.0000"                    ' Quantity Shipped

OESHI1detail1.Update

OESHI1detail1Fields("LINENUM").PutWithoutVerification ("-2")          ' Line Number

OESHI1detail1.Read
OESHI1headerFields("CREATEINV").Value = "1"                           ' Create Invoice from Shipment

OESHI1headerFields("OECOMMAND").Value = "4"                           ' Process O/E Command

OESHI1header.Process
temp = OESHI1header.Exists
OESHI1header.Insert
OESHI1header.Read
OESHI1detail1Fields("LINENUM").PutWithoutVerification ("32")          ' Line Number

OESHI1detail1Fields("LINENUM").PutWithoutVerification ("32")          ' Line Number

OESHI1detail1.Read


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

Question 1- how do I know the

OESHI1detail1Fields("LINENUM").PutWithoutVerification ("-1")          ' Line Number

values?

question 2- can I trim the above VBA ??

Any insight would be greatly appreciated...

Cheers
Johnny

 

[ettienne]

You can clean that up a lot, a lot of crap is recorded.

 

[Jay Converse]

You don't need code. Tell the users to press the "Ship All" button, then "Post".

 

[Johnny48]

I found some code and modified a bit...

Sub MainSub()

OESHI1detail5.Compose Array(OESHI1header)


OESHI1header.Cancel
OESHI1header.Init
OESHI1headerFields("ORDNUMBER").Value = " &  sheet1.cells(1,1)& " ' Order Number to generate SHIPMENT and INOICE for...


for i = 1 to LastRow
OESHI1detail1.Init
OESHI1detail1Fields("ITEM").Value = " & sheet1 & cells(r,2) &" ' Item
OESHI1detail1Fields("LOCATION").Value = "& sheet1 & cells(r,4) &" ' Location
OESHI1detail1Fields("QTYSHIPPED").Value = "& sheet1 & cells(r,3) &" ' Quantity Shipped
OESHI1detail1.Insert
OESHI1detail1Fields("LINENUM").PutWithoutVerification ("-1") ' Line Number
OESHI1detail1.Read
next

OESHI1headerFields("CREATEINV").Value = "1" ' Create Invoice from Shipment?

OESHI1header.Process
OESHI1header.Insert
OESHI1header.Read
my_invoice = OESHI1headerFields("INVNUMBER").Value
my_shipment = OESHI1headerFields("SHINUMBER").Value

msgbox "Shipment No.: " & my_shipment & vbnewline & "Invoice No.: " & my_invoice 
End Sub

How would I set up the connection to the ACCPAC company ?

Cheers,
Johnny

 

[ettienne]

Accpac VBA macros are connected to the Accpac company you are running the macro from.

 

[Johnny48]

Yes, but I want to use the above code form Excel...

 

[Jay Converse]

Why are you doing this? You're just making extra work for your users. If they have to type order numbers into a spreadsheet, they might as well use the regular shipment UI.

 

[ettienne]

Make your life easier and run the macro from Accpac. You can do everything in an Accpac macro that you can do in an Excel macro.