excelvbaaccpac

ACCPAC and Excel Spreadsheets


Is there a way to reference ACCPAC in VBA for Excel 2010?

I am wondering if information from a spreadsheet could be programatically entered into our accounting database by running a macro from within Excel. I have just been studying a macro I recorded while entering a new customer in a test database in ACCPAC. Our operations centers store information in spreadsheets that I would like to be able to get into the database without re-keying the data.

I'm also open-minded for solutions that see ACCPAC getting data from the spreadsheets as opposed to the spreadsheet macros creating ACCPAC connections and objects.

It's not a super-involved project - we just want something to cut down on redundancy.


Solution

  • There are two ways to do what you're looking for without going the import route. Both involve VBA programming. Sage ERP 300 (or Accpac as we all know it by) can be controlled via COM. That means that you can record a macro of the process that you want to automate or control in Accpac and you'll get VBA code that will give you a good starting point. You can then bring that code into the VBA code within your Excel workbook. You will be missing one portion however. When you start up Accpac from within Excel you need to create a new session; telling Accpac your user ID, password, the company that you want to connect to and the session date. With that AccpacSession COM object you can then use the recorded VBA code to automate Accpac and push the information that you have in your Excel workbook into Accpac.

    Here's an example of how to create an AccpacSession object:

    Public AccSession As AccpacSession
    Public AccSessMgr As AccpacSessionMgr
    Public AccDBLink As AccpacDBLink
    Private mlSessionID As Long
    Private mstrObjectHandle As String
    '-----------------------------------------------------------
    
    Public Function OpenAccpacSession(Optional sCompany As String) As Boolean
    
        OpenAccpacSession = False
        mstrServerName = ""
        If AccSessMgr Is Nothing Then
            Set AccSessMgr = CreateObject("Accpac.SessionMgr")
    
            With AccSessMgr
                .AppID = "XY"
                .AppVersion = "61A"
                .ProgramName = "XY0001"
    
                .ServerName = mstrServerName
            End With  ' mSessMgr
        End If
    
        If AccSession Is Nothing Then
            AccSessMgr.CreateSession mstrObjectHandle, mlSessionID, AccSession
        End If
    
        If AccSession Is Nothing Then
            OpenAccpacSession = False  ' user couldn't sign on
        Else
            OpenAccpacSession = AccSession.IsOpened
        End If
    
        If OpenAccpacSession = True Then
            Set AccDBLink = AccSession.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
        End If
    
    End Function
    

    Now you'll be able to use the AccDBLink object to open up views to import the data into Accpac.

    The other way is to take the VBA macro that you recorded in Accpac and add in code to control Excel.

    Import templates will work fine but they can be a challenge to get set up right and if there is a problem with validation during import it can sometimes be tricky to figure out where and what the problem is. By going the VBA route you can do your own validation of the data, incorporating your own business' rules and be able to give more informative error messages to the user.

    Edit: From within Excel's VBA environment you'll add references. All of the Accpac com objects that you'll need for this part are prefixed with "ACCPAC". Specifically you'll want ACCPAC COM API Object 1.0 and ACCPAC Session Manager 1.0.