Real-time Acctivate data in Excel

An Excel spreadsheet can be setup to display information dynamically linked from the Acctivate database. This allows you to view information in a spreadsheet that can be refreshed to view real-time information from Acctivate at anytime. There are two parts to this process: creating a data source and designing the query to return data from the source into Excel.

Setting up the Data Source

  1. Create a new spreadsheet in Microsoft Excel.
  2. Start a New Database Query by clicking the Data tab, then choosing From Other Sources > From Microsoft Query
    • Excel 2010: On Data tab Click From Other Sources > From Microsoft Query  Microsoft Query
    • Excel 2016: On Data tab Click Get Data > From Other Sources > From Microsoft Query  
  3. Select <New Data Source> and click OK.
    new data source
  1. Enter a name for the data source (i.e. Acctivate, or Acctivate_YourCompany), then choose SQL Server as the driver for the type of database you want to access. When finished, click “Connect…”
    create new data source
  2. In the SQL Server Login window, select your SQL Server Acctivate instance from the drop down list. This is generally the name of your server, followed by \ACCTIVATE.
    sql server login
  3. Uncheck the “Use Trusted Connection” option, then enter the login information for your database. (If you need this information, contact support.) When finished, click “Options >>” to expand the default database options.
    sql server login options
  4. Select your Acctivate database from the Database drop down menu. This is generally labeled “ACCTivate$CompanyName” (in the screenshot above, the demo database is used as an example). Leave all other fields as is, then click “OK”.
  5. Excel will test the connection to your data source. If everything is correct, you will now be able to run Microsoft Query on this workstation to return data from Acctivate into Excel.

Returning Data from the Data Source into Excel

Now that the Acctivate data source has been created, you can run Excel Queries from the workstation that the database connection was created on. To access individual data views:

  1. Start a New Database Query by clicking the Data tab, then choosing From Other Sources > From Microsoft Query
  2. Select the Acctivate data source, then click “Ok” to open the Microsoft Query window
  3. Here, you can select individual tables to return data from. Select dbo from the Owner drop down list to see only tables and views pertaining to your company data.
    add tables
  4. Select a data table from the list, then click “Add” to add it to the query. In this example, we will use the Customer view.
  5. Once the data view has been added to the query, you can click and drag fields into the Microsoft Query window. Clicking and dragging a field name to the Query window will return all data contained in that field within a particular data view.
    microsoft query 2
  6. You can filter the query by any of the selected fields. These options are similar to the report filters in Acctivate. Click Next when you are done.
  7. When ready, click the open door icon to return query data to Excel.
    return data to excel
  8. Select the worksheet and cell where you would like to place the imported data and click OK.
    import data in excel
  9. To edit the query, right click on a cell within the data range, then click Table > Edit Query… to open the Microsoft Query window again.
    edit query
  10. Once you have created your query and saved the spreadsheet, the data will be refreshed each time you open this file in Excel. You can also click the Refresh button on the Data tab to reload the data from Acctivate.
Posted in Database, Reports - Last modified on May 18, 2017Eric Cartrite
Still need help?
Search all content on this site, contact support at 817-870-1311, or create a support request.