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.
- 1 Documentation
- 2 Requirements
- 3 Running Excel
- 4 Troubleshooting
- 5 Contact
Documentation is written into the 'master' Excel workbook.
Software, Network & Computer Server Requirements
- A host server that:
- Has access to DataSelf's data warehouse.
- Has a working copy of Microsoft Excel or Excel 365 installed.
- The Excel COM object is installed. (See Excel COM Objects.)
- Has a email server or is configured to allow access to a SMTP server.
- A SMTP server that can forward outgoing emails with attachments.
- Credentials to the SMTP server (user name, password).
- A reliable network / internet connection between servers as appropriate.
- 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
Datatab on ribbon >
Connections > Properties > Enable Backgroup Refreshcheckbox
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.
A .vbs script (VBScript) may be used to run the Excel workbook containing the Excel AD macro 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
$filepath = "C:\projects\excelexport.xls" $excel = New-Object -ComObject Excel.Application $workbook = $excel.Workbooks.add() $worksheetA = $workbook.Worksheets.Add()
Excel COM Objects
- Warning: Excel may not work correctly in a scheduled job unless these configuration changes are applied.
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
CW was the primary designer and programmer for this module.