Excel Reporting and Distribution Module

From DataSelf Knowledge Base
Jump to navigation Jump to search

A Microsoft Excel workbook programmed with Excel macros. The core component of Excel AD is a VBA macro that runs inside a Microsoft Excel workbook.


Documentation is written into the 'master' Excel workbook.


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 or Excel 365 installed.
    3. The Excel COM object is installed. (See Excel COM Objects.)
    4. 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.

Running Excel

A .vbs script (VBScript) may be used to run the Excel workbook containing the Excel AD macro code.

VBScript Code

 fullFileReference = "E:\Program Files (x86)\DataSelf\ExcelDistribution_YDT Sales export\DS_ExcelADController_YTDCustomerSalesActivity_BySalesPerson.xlsm"
 set apExcel = CreateObject("Excel.Application")
 set wkBook = apExcel.Workbooks.Add (fullFileReference)
 wscript.echo "Open workbook " & fullFileReference
 apExcel.run "Main", fullFileReference, True

PowerShell Code

 $filepath = "C:\projects\excelexport.xls"
 $excel = New-Object -ComObject Excel.Application
 $workbook = $excel.Workbooks.add()
 $worksheetA = $workbook.Worksheets.Add()

Excel COM Objects

See Configuring COM for Excel.

Warning: Excel may not work correctly in a scheduled job unless these configuration changes are applied.

The CreateObject("Excel.Application") instantiates a Component Object Model (COM) object.

  • The COM object for Excel should be installed along with Excel.

The Powershell equivalent to CreateObject("Shell.Application") in VBScript is $objShell = New-Object -COMObject "Shell.Application" in Windows PowerShell.


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


CW was the primary designer and programmer for this module.