Excel Spreadsheet Automation with the QUERY Formula

Pull data, automate spreadsheets, and more with the QUERY formula.

The CData Excel Add-In for Sage US provides formulas that can edit, save, and delete Sage US data.The following three steps show how you can automate the following task: Search Sage US data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

  • Query: The declaration of the Sage US data records you want to retrieve or the modifications to be made, written in standard SQL.
  • Connection: Either the connection name, such as Sage50USConnection1, or a connection string. The connection string consists of the required properties for connecting to Sage US data, separated by semicolons.

    The Application Id and Company Name connection string options are required to connect to Sage as a data source. You can obtain an Application Id by contacting Sage directly to request access to the Sage 50 SDK.

    Sage must be installed on the machine. The Sage.Peachtree.API.dll and Sage.Peachtree.API.Resolver.dll assemblies are required. These assemblies are installed with Sage in C:\Program Files\Sage\Peachtree\API\. Additionally, the Sage SDK requires .NET Framework 4.0 and is only compatible with 32-bit applications. To use the Sage SDK in Visual Studio, set the Platform Target property to "x86" in Project -> Properties -> Build.

    You must authorize the application to access company data: To authorize your application to access Sage, restart the Sage application, open the company you want to access, and connect with your application. You will then be prompted to set access permissions for the application in the resulting dialog.

    While the compiled executable will require authorization only once, during development you may need to follow this process to reauthorize a new build. To avoid restarting the Sage application when developing with Visual Studio, click Build -> Configuration Manager and uncheck "Build" for your project.

  • ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

  1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search Sage US data, such as Name.
  2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
  3. =CDATAQUERY("SELECT * FROM Customer WHERE Name = '"&B3&"'","ApplicationId="&B1&";CompanyName="&B2&";Provider=Sage50US",B4)
  4. Change the filter to change the data.