Ready to get started?

Learn more about the CData Excel Add-In for UPS or download a free trial:

Download Now

Excel Spreadsheet Automation with the QUERY Formula

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

The CData Excel Add-In for UPS provides formulas that can edit, save, and delete UPS data.The following three steps show how you can automate the following task: Search UPS 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 UPS data records you want to retrieve or the modifications to be made, written in standard SQL.
  • Connection: Either the connection name, such as UPSConnection1, or a connection string. The connection string consists of the required properties for connecting to UPS data, separated by semicolons.

    The driver uses five pieces of information in order to authenticate its actions with the UPS service.

    • Server: This controls the URL where the requests should be sent. Common testing options for this are: https://wwwcie.ups.com/ups.app/xml and https://wwwcie.ups.com/webservices
    • AccessKey: This is an identifier that is required to connect to a UPS Server. This value will be provided to you by UPS after registration.
    • UserId: This value is used for logging into UPS. This value is the one you chose to login with when registering for service with UPS.
    • Password: This value is used for logging into UPS. This value is the one you chose to login with when registering for service with UPS.
    • AccountNumber: This is a valid 6-digit or 10-digit UPS account number.
    • PrintLabelLocation: This property is required if one intends to use the GenerateLabels or GenerateReturnLabels stored procedures. This should be set to the folder location where generated labels should be stored.
  • 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 UPS data, such as SenderID.
  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 Senders WHERE SenderID = '"&B6&"'","Server="&B1&";AccessKey="&B2&";Password="&B3&";AccountNumber="&B4&";UserId="&B5&";Provider=UPS",B7)
  4. Change the filter to change the data.