ACCPAC和Excel电子表格

问题描述

是否可以在VBA for Excel 2010中引用ACCPAC?

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

我想知道是否可以通过在Excel中运行宏将电子表格中的信息以编程方式输入到我们的会计数据库中.我刚刚研究了在ACCPAC的测试数据库中输入新客户时记录的宏.我们的运营中心将信息存储在电子表格中，我希望能够在不重新键入数据的情况下进入数据库.

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.

对于那些看到ACCPAC从电子表格获取数据而不是创建ACCPAC连接和对象的电子表格宏的解决方案，我也持开放态度.

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.

推荐答案

有两种方法可以完成您要寻找的内容而无需执行导入路线.两者都涉及VBA编程.Sage ERP 300(或我们众所周知的Accpac)可以通过COM进行控制.这意味着您可以在Accpac中记录要自动化或控制的过程的宏，并且您将获得VBA代码，这将为您提供一个良好的起点.然后，您可以将该代码带入Excel工作簿的VBA代码中.但是，您将缺少一部分.当您从Excel中启动Accpac时，您需要创建一个新会话.告诉Accpac您的用户ID，密码，您要连接的公司以及会话日期.然后，通过该AccpacSession COM对象，您可以使用记录的VBA代码自动执行Accpac，并将Excel工作簿中的信息推送到Accpac.

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.

以下是如何创建AccpacSession对象的示例:

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

现在，您将能够使用AccDBLink对象打开视图以将数据导入Accpac.

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

另一种方法是采用您在Accpac中记录的VBA宏，并添加代码以控制Excel.

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

导入模板可以很好地工作，但是要正确设置它们可能是一个挑战，如果在导入过程中验证存在问题，有时找出问题所在和原因可能很棘手.通过使用VBA路线，您可以对数据进行自己的验证，并结合自己公司的规则，并能够向用户提供更多信息错误消息.

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.

在Excel的VBA环境中，您将添加引用.该部分所需的所有Accpac com对象都以"ACCPAC"为前缀.具体来说，您将需要ACCPAC COM API对象1.0和ACCPAC会话管理器1.0.

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.

这篇关于ACCPAC和Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！