Excel Reporting and Distribution Module

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

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 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.

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.

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.