Excel Reporting and Distribution Module

From DataSelf Knowledge Base
Jump to: navigation, search

{{#breadcrumb: Main Page}}

A Microsoft Excel workbook programmed with Excel macros.

Documentation

Documentation is written into the 'master' Excel workbook.

Requirements

Software, Network & Computer Server Requirements

  1. A host server that:
    1. Has access to DataSelf's data warehouse.
    2. Has a working copy of Microsoft Excel installed.
    3. Has a email server or is configured to allow access to a SMTP server.
  2. A SMTP server that can forward outgoing emails with attachments.
    1. Credentials to the SMTP server (user name, password).
    2. A reliable network / internet connection between servers as appropriate.
    3. Network security between servers that will allow access to the SMTP server. (Getting security configured to allow this has been an issue in some previous installations.)

SMTP Server Connection Configuration

Settings used by Excel AD.

  • SMTP Address -- e.g. "smtp.gmail.com"
  • SMTP User Name
  • SMTP Password
  • SMTP Requires authentication (Yes/No)
  • Use SSL for the connection (True/False)
  • SMTP Server Port (default = 25)
  • Sender email address -- used for the 'sent from' address

Enable Background Refresh option Must be Off

Data tab on ribbon > Connections > Properties > Enable Backgroup Refresh checkbox

The Enable Backgroup Refresh option must be disabled (the checkbox must be blank) on all master files. Improper setting of this option on master filess can cause hard to diagnose errors. Master files are the Excel files usually found in the folder named Master.

Symptoms: The usual system is that the refresh process 'hangs' while processing a workbook and goes into an infinite loop.

Troubleshooting

The Log Column: The First Place to Turn

See the Log column in the Excel workbook. A status message or error message should be written to every row of Excel file information every time the module is run. This should be the first place to look for error messages. Please help us improve the software and it's documentation -- please record this information every time there is a problem can cannot easily be resolved.

The module is designed to track the success or failure of each Excel file processed and to write a status message or a an error message to the Log column. The macros have been programmed to respond to a number of abnormal situations (things that can go wrong) with helpful error messages.

Problems with Excel Lock Files ( " … is locked for editing by …" message)

Problems with SQL Server Agent Calling Excel Files

Process 'hangs' in an infinite loop

See Enable Background Refresh option must be disabled on master file

Contact

CW was the primary designer and programmer for this module.