I want to create a simple macro that does a bank transfer

SOLVED

It would need to prompt for from bank, to bank, amount, and transfer date.

then offer OK or cancel.

The rest of the stuff on the bank transfer screen I find not helpful. 

Parents
  • 0
    SUGGESTED

    start recording a macro and go to the screen and do one transaction

    then stop recording

    you will then have the basic code  most of the time.

    after that it's up to  you. you should not have serious technical issues that screen is fairly simple.

  • 0 in reply to Ron Boyd

    Nope, bank modules don't record, like many 3rd party products.  You need to roll your own code using the BK0036 and BK0835 objects.

  • 0 in reply to Jay Converse Acumen
    SUGGESTED

    I went to Bank Services, Bank Transfers

    If this is the topic of conversation - they record in my universe as of 5 minutes ago

    Sounds like the benefits of not upgrading your work computer too much

    Here you go Peter

    Sub MainSub()
    '
    ' Sage Accpac Macro file: C:\TEMP\JUNK.avb
    ' Recorded at: Mon Nov 18 19:11:18 2019
    '

    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 BKTRANSFER1header As AccpacCOMAPI.AccpacView
    Dim BKTRANSFER1headerFields As AccpacCOMAPI.AccpacViewFields
    mDBLinkCmpRW.OpenView "BK0036", BKTRANSFER1header
    Set BKTRANSFER1headerFields = BKTRANSFER1header.Fields

    Dim BKTRANSFER1detail As AccpacCOMAPI.AccpacView
    Dim BKTRANSFER1detailFields As AccpacCOMAPI.AccpacViewFields
    mDBLinkCmpRW.OpenView "BK0835", BKTRANSFER1detail
    Set BKTRANSFER1detailFields = BKTRANSFER1detail.Fields

    BKTRANSFER1header.Compose Array(BKTRANSFER1detail)

    BKTRANSFER1detail.Compose Array(BKTRANSFER1header)


    Dim BKPROC2 As AccpacCOMAPI.AccpacView
    Dim BKPROC2Fields As AccpacCOMAPI.AccpacViewFields
    mDBLinkCmpRW.OpenView "BK0105", BKPROC2
    Set BKPROC2Fields = BKPROC2.Fields



    BKPROC2Fields("PROCESS").PutWithoutVerification ("12")                ' Bank Process

    BKPROC2.Process
    BKTRANSFER1header.Init

    BKTRANSFER1headerFields("DESC").Value = "dddd"                        ' Description

    BKTRANSFER1headerFields("REFERENCE").Value = "rrrrrrrrrr"             ' Reference

    BKTRANSFER1headerFields("OBANK").Value = "FCBANK"                     ' Transfer Bank

    BKTRANSFER1headerFields("DBANK").Value = "PRBANK"                     ' Deposit Bank

    BKTRANSFER1headerFields("OSAMOUNT").Value = "1.000"                   ' Transfer Source Amount
    BKTRANSFER1detail.Browse "", 1
    BKTRANSFER1detail.Fetch
    BKTRANSFER1detail.Fetch
    BKTRANSFER1detail.Fetch
    BKTRANSFER1header.Insert
    BKPROC2Fields("OPERATION").PutWithoutVerification ("1")               ' Process Status
    BKPROC2.Process



    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

  • 0 in reply to Ron Boyd

    Good deal, they didn't record years ago, I've been rolling my own for too long.

  • 0 in reply to Ron Boyd

    It works great except that I want the macro to prompt for banks, date, and amount. I actually got this far myself, but I don't know how to prompt for stuff in macros. If you have a recommended course or book, that would also be very helpful. I am great at programming but don't know VB at all. 

  • +1 in reply to Peter.Heinicke
    verified answer

    edit the macro from Sage 300

    now use the help menu

    look for things like INPUTBOX, MSGBOX etc

    I recommend Google; there are examples everywhere of all kinds of stuff for VBA

  • +1 in reply to Ron Boyd
    verified answer

    I literally wrote this same macro the other night.

    One thing to watch is that the macro doesn't record the date. You'll want to include POSTDATE as a value, otherwise all of your transactions will be as of the session date.  And then you get to learn how to edit data in the BK tables.

  • 0 in reply to Ron Boyd
    SUGGESTED

    Sub MainSub()
    '
    ' Sage Accpac Macro file: C:\TEMP\JUNK.avb
    ' Recorded at: Mon Nov 18 19:11:18 2019
    '

    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 BKTRANSFER1header As AccpacCOMAPI.AccpacView
    Dim BKTRANSFER1headerFields As AccpacCOMAPI.AccpacViewFields
    mDBLinkCmpRW.OpenView "BK0036", BKTRANSFER1header
    Set BKTRANSFER1headerFields = BKTRANSFER1header.Fields

    Dim BKTRANSFER1detail As AccpacCOMAPI.AccpacView
    Dim BKTRANSFER1detailFields As AccpacCOMAPI.AccpacViewFields
    mDBLinkCmpRW.OpenView "BK0835", BKTRANSFER1detail
    Set BKTRANSFER1detailFields = BKTRANSFER1detail.Fields

    BKTRANSFER1header.Compose Array(BKTRANSFER1detail)

    BKTRANSFER1detail.Compose Array(BKTRANSFER1header)


    Dim BKPROC2 As AccpacCOMAPI.AccpacView
    Dim BKPROC2Fields As AccpacCOMAPI.AccpacViewFields
    mDBLinkCmpRW.OpenView "BK0105", BKPROC2
    Set BKPROC2Fields = BKPROC2.Fields

    BKPROC2Fields("PROCESS").PutWithoutVerification ("12") ' Bank Process

    BKPROC2.Process
    BKTRANSFER1header.Init

    BKTRANSFER1headerFields("DESC").Value = InputBox("descrip") ' Description

    BKTRANSFER1headerFields("REFERENCE").Value = InputBox("ref") ' Reference

    BKTRANSFER1headerFields("OBANK").Value = InputBox("xfer bank", , "CCB") ' Transfer Bank

    BKTRANSFER1headerFields("DBANK").Value = InputBox("deposit bank", , "FCBANK") ' Deposit Bank

    BKTRANSFER1headerFields("OSAMOUNT").Value = InputBox("amt", , 0) ' Transfer Source Amount

    BKTRANSFER1headerFields("POSTDATE").Value = InputBox("date", , Date) ' Transfer date

    BKTRANSFER1detail.Browse "", 1
    BKTRANSFER1detail.Fetch
    BKTRANSFER1detail.Fetch
    BKTRANSFER1detail.Fetch
    BKTRANSFER1header.Insert
    BKPROC2Fields("OPERATION").PutWithoutVerification ("1") ' Process Status
    BKPROC2.Process

    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

  • 0 in reply to Peter.Heinicke

    I know this is a silly question at this point but what is it about the standard transfer screen that you're trying to improve? 

    Dana

  • 0 in reply to Dana B. Stidsen

    Not silly at all. I wait for months to reconcile statements and as a result often have 25 or more transfers to enter. They are all bank 1 to bank 2 and vice versa. The only thing that changes is the amount and the date, so a macro really speeds things up. I hard code the macro to enter the bank 1 and bank 2 and let it prompt me for the date, and away I go. 

Reply
  • 0 in reply to Dana B. Stidsen

    Not silly at all. I wait for months to reconcile statements and as a result often have 25 or more transfers to enter. They are all bank 1 to bank 2 and vice versa. The only thing that changes is the amount and the date, so a macro really speeds things up. I hard code the macro to enter the bank 1 and bank 2 and let it prompt me for the date, and away I go. 

Children
No Data