Import a database table into Microsoft Excel 2010

The following instructions can be used to set up a Microsoft Excel 2010 spreadsheet to display information dynamically linked from a data table in the Acctivate database. The data can be refreshed to view real-time information from Acctivate at anytime.

  1. Create a new spreadsheet in Microsoft Excel
  2. Click the Data tab (at the top), then click From Other Sources and select From SQL Server
    excel 1
  3. Connect to Database Server
    1. Enter the Acctivate SQL Server name. You can find the SQL Server name by looking at “Database Sources” in the Acctivate Database Maintenance  utility.
      database sources
    2. Enter the SQL Server User ID and Password. The User ID is typically sa . You will need to obtain the password from the Acctivate Support Team if you don’t have the password for the sa account. Click Next to proceed.
      excel 2
  4. Select Database and Table
    1. Select the “ACCTivate$xxxxx” database (replacing xxxxx with your company). In this example, we’ll use the ACCTivate$Demo database.
    2. Un-check “Connect to a specific table” to include all tables, then click Next.excel 3
  5. Save Data Connection File and Finish
    1. Type a brief Description, like “Connection to Acctivate database”, then click Finish.excel 4
  6. Select Table
    1. Select a Table or View to display on the worksheet.  Please check out our list of data views that are available.We recommend that you use a database VIEW that does not begin with the “tb”prefix. The internal tables use the “tb” prefix and are subject to change.Using the database views will minimize the possibility of changes to the database that require an update to the spreadsheet.
      excel 5
  7. Import Data
    1. Typically, you will want to simply display the data into the Excel as a Table . However, advanced options, such as PivotTable Report  are very powerful tools.
    2. Using the default “Existing worksheet” option will place the data into the worksheet, starting at the selected cell (typically $A$1)
    3. Click OK to load the table data
      excel 6

At this point, you should see a data grid in Excel containing information from the selected Acctivate data table.
excel 7

Tagged with: ,
Posted in Database - Last modified on May 19, 2016Eva Rhees
Still need help?
Search all content on this site, contact support at 817-870-1311, or create a support request.