Microsoft – Excel – SubTotals

Background

I have a situation where I need to summarize some data and I don’t really want to do so in a database.

 

SQL Server

In Database we have roll-up and Windowing Functions.

And, it is really likely the way I should go.

 

Excel

But, as the data is not in a database, I will really rather not bring it in.

I will just use Excel and send the Excel file to our business partner.

 

Data in Excel

Menu

Menu – Data

Menu – Data – Outline

 

SubTotal

Subtotal – Process – Category – Country

Click the Data menu option.

Select the range of data by clicking on the column header as well on last data row.

Click the Subtotal menu option…

Subtotal –  Operation

Image

Explanation

  1. At each change in : Country
  2. Use function :- Sum
  3. Add Subtotal to : Population
  4. Replace current options :- Checked
  5. Page break between groups :- Not Checked
  6. Summary below data :- Checked

 

Subtotal –  Results

Image

Explanation

  1. We have subtotals for our two countries, Canada & US
  2. And, we have a Grand Total

Subtotal – Process – Category – State

Subtotal –  Operation

Image

Explanation

  1. At each change in : State
  2. Use function :- Sum
  3. Add Subtotal to : Population
  4. Replace current options :- Not Checked
  5. Page break between groups :- Not Checked
  6. Summary below data :- Checked

 

Subtotal –  Results

Image

Explanation

  1. We have subtotals for State/Regions
  2. Cumulative subtotals for the countries
  3. And, we have a Grand Total

 Summary

To add secondary sub-totals please make sure to un-check “Replace current options” in the subtotal window.

 

Powershell Modules – AzureAD – “Get User’s Information “

Background

Now that we have “AzureAD” Powershell modules installed, let us play around with it.

First off, the let us review what type of information is returned on a Member Account.

 

Code

Outline

  1. Using CmdletBinding define our script argument list
  2. To connect to our “Tenants Domain“, Issue “Connect-AzureAD
  3. To get specific AD Accounts, call get-azureaduser and filter appropriately
    • Get the returned object type by issuing GetType().Fullname
    • Get the first item in the result list by issuing Select-Object -first 1
    • Get Object’s Property list by issuing Select-Object -Property *
    • Display PropertyList

 

Actual Code

 


[CmdletBinding()]
Param(
  [Parameter(Mandatory=$True,Position=1)]
   [string]$personName
	
)
Set-StrictMode -Version Latest;

#Connect to Azure AD
Connect-AzureAD

# get list of AD users that matches the name passed in
$objListofPerson = get-azureaduser -Filter "startswith(displayName,'$personName')"


# if List is empty, then say so
if (!$objListofPerson)
{
     "Object (objListofPerson) is null (empty)"
     return
}
 
# Keith Hill - Get Type name
# the-typename-and-inheritance-chain/
# http://rkeithhill.wordpress.com/2007/10/28/powershell-quicktip-using-pstypenames-to-see-# 
$strLog = "Type name is " + $objListofPerson.GetType().Fullname;
$strLog
 
# Get top item in list
$objPerson = $objListofPerson | Select-Object -first 1

# if List is empty, then say so
if (!$objPerson)
{
     "Object (objPerson) is null (empty)"
     return
}
 
#https://www.codykonior.com/2013/03/26/powershell-how-to-show-all-of-an-objects-properties-and-values/
$objPersonPropList = $objPerson | Select-Object -Property *

if (!$objPersonPropList)
{
	 "Object has no properties"
	 return
}

$objPersonPropList

Invoke

Syntax


powershell -file ./Office365AzureAccount.ps1 -personName "[personName]"

Sample


powershell -file ./Office365AzureAccount.ps1 -personName "Daniel Adeniji"

Output

Images

Image – 01

Image – 02

Image – 03

Image – 04

 

Tabulate

  1. Type
    • Type name is Microsoft.Open.AzureAD.Model.User

Source Code

GitHub

DanielAdeniji/Office365AzureADPowerShell
Link

 

References

  1. Microsoft Azure
    • Azure/Azure PowerShell
      • Connect-Azure
        Link
      • GetAzureADUser
        • GetAzureADUser
          Link
  2. PowerShell
    • PowerShell Parameters
      • Don Jones
        • Windows PowerShell: Defining Parameters
          Link
    • PowerShell Properties
      • Cody Konior
        • PowerShell: How to show all of an object’s properties and values
          Link
    • PowerShell List
      • Don Jones
        • How can I get just the first entry from a list of entries?
          Link
  3. AzureAD Powershell
    • GetAzureADUser
      • StackOverflow
        • Example of Get-AzureADUser [-Filter <String>] command
          Link

Powershell Modules – AzureAD – Installation

Background

Needing to do some minimal Microsoft Office 365 work.

Found out that I need Azure Powershell Modules.

Requirement

The Azure Active Directory ( AD) Powershell Modules are available on Microsoft’s Powershell Gallery.

Here is Microsoft’s write-up of what it takes to access the gallery and download modules from it:

Image

Azure Active Directory PowerShell for Graph
Link

Tabulate

Product Version Prerequisite
Operating System ( OS) Windows 10
Windows Management Framework ( WMF ) WMF v5.0
PackageManagement PowerShell Modules Preview – March 2016 v1.1 Powershell v3.0 or v4.0

 

Do we meet the requirement?

Let us do a quick check to determine whether we meet the requirement.

OS version

WinVer

From Console, issue winver

Syntax

winver

Output

Explanation

We are looking to have Windows Version 10, we are only at Version 7.

 

WMI Version

WMI Version – Using Powershell

From Console, run powershell and query $PSVersionTable

Syntax

Powershell -Command "$PSVersionTable"

Output

Explanation

We are looking to have WSManStackVersion be at v5.0, but we are only at v3.0.

 

Powershell – Module – PackageManagement

PackageManagement Powershell Modules

In Powershell issue “Get Module -ListAvailable

Syntax

powershell -Command "Get-Module -Name *Package* -ListAvailable"

Output

Explanation

We appear to be good with the Powershell Module Package Management.  The version installed is 1.*.

 

Meeting Requirement

If we did not meet the requirements, here are options to do so.

Windows Management Framework ( WMF )

Version

Version 5.1

As of 2018-Jan-21st, the latest version of WMF is 5.1 and it is available here.

Powershell – Module – PackageManagement

Go here and download the module that fits your your OS bitness.

 

Review Installation

Current

Let us quickly see whether we have the Azure Powershell Module installed.

Syntax

The module’s name is AzureAD and so we can look for anything bearing the Azure moniker.


powershell -Command "Get-Module -Name *Azure* -ListAvailable"

Output

Explanation

Nada

Actual Installation

Syntax


powershell -command "Install-module AzureAD"

Output

Explanation

We confirmed that we are OK with downloading modules from PSGallery.

 

Installation Validation

Syntax


Powershell -Command "Get-Module -Name *Azure* -ListAvailable"

Output

Explanation

We now have AzureAD installed.

The current version is 2.0.x.x

 

References

  1. Microsoft Azure
    • Azure PowerShell
      • Azure Active Directory PowerShell for Graph
        • Installation
        • How can I find the version of the Azure AD PowerShell module I’m using?
          Link

Integration Services – Error – “SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37”

 

Error

Error Image

Error Text

Started:  6:15:02 AM
Error: 2018-01-18 06:15:07.61
Code: 0xC0202009
Source: Read Excel Data Excel Source File [49]
Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.
End Error
Error: 2018-01-18 06:15:07.61
Code: 0xC02020E8
Source: Read Excel Data Excel Source File [49]
Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.
End Error
Error: 2018-01-18 06:15:07.65
Code: 0xC004706B
Source: Read Excel Data SSIS.Pipeline
Description: "Excel Source File" failed validation and returned validation status "VS_ISBROKEN".
End Error
Error: 2018-01-18 06:15:07.65
Code: 0xC004700C
Source: Read Excel Data SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2018-01-18 06:15:07.65
Code: 0xC0024107
Source: Read Excel Data
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  6:15:02 AM
Finished: 6:15:08 AM
Elapsed:  6.422 seconds

 

TroubleShooting

SQL Server Agent

Job Step

Image

Tabulate

Item Value Breakdown
Connection String Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Import\Sample.xlsx;Extended Properties=”EXCEL 12.0 XML;HDR=YES”;
Provider = Microsoft.ACE.OLEDB.12.0
Extended Properties=”EXCEL 12.0 XML;HDR=YES”
Extended Properties=”EXCEL 12.0 XML;HDR=YES”

 

Remediation

Microsoft Access Database Engine 2010 Redistributable

Guidance

Link

Step

Download and apply “Microsoft Access Database Engine 2010 Redistributable“.  It is available here.

We are on a 64-bit machine and so we downloaded the 64-bit version.

Restart the machine or just the related services ( SQL Server Engine, SQL Server Agent and Integration Services ).

VBScript – Sample Microsoft Office Access ( MS Access ) – TroubleShooting

Background

Here are some areas to review if you run into problems accessing a Microsoft Office Access DB via an OLE-DB Provider.

 

TroubleShooting

Microsoft

Task Manager

Image

Explanation

  1. Bitness
    1. On a 64-bit machine, make sure you are running as a 32-bit app

Resource Monitor

Image

Highlight

  1. C:\Program Files (x86)\Common Files\Microsoft Shared\Office14
    • ACEOLEDB.DLL
  2. C:\Program Files (x86)\Common Files\System\ado
    • msado15.dll
    • msadrh15.dll
  3. C:\Program Files (x86)\Common Files\System\OLE DB
    • oledb32.dll
    • oledb32r.dll

 

Registry

Microsoft.ACE.OLEDB.*

Search the registry for Microsoft.ACE.OLEDB

Image

UDL File

  1. National Instruments
    • Creating a Microsoft Data Link file (UDL) for Connecting to a Database in LabVIEW
      Link
  2. Microsoft
    • Microsoft | Developer
      • Chaitanya Medikonduri
        • How to run 32-bit UDL file on a 64-bit Operating System
          Link

 

VBScript – Sample Microsoft Office Access ( MS Access ) App

Background

Here is a code snippet for using VBScript to develop a small application that connects to MS Access database.

BTW, the MS Access database was created using the Create Database Wizard in Access.

Code

Script



option explicit

on error resume next

Dim CONNECTION_STRING
Dim objConn
Dim objRS

Dim strRow
Dim strData

Dim strLog

Const FILE_FOLDER="database\"
Const FILE_NAME="AddressBookDatabase.mdb"
Const PROVIDER="Microsoft.ACE.OLEDB.12.0;"

strData = ""

CONNECTION_STRING = "Provider=" & PROVIDER & ";Data Source=" & FILE_FOLDER & "" & FILE_NAME

strLog = "Connection String :- " & CONNECTION_STRING & vbCrLf
wscript.echo strLog
	
'Define object type
Set objConn = CreateObject("ADODB.Connection")
 
'Open Connection
objConn.open CONNECTION_STRING

if (Err.Number <> 0)  Then

	strLog = ""
	strLog = strLog & "Error" & vbCrLf
	strLog = strLog & "====" & vbCrLf	
	strLog = strLog & vbTab & "Connection String :- " & CONNECTION_STRING & vbCrLf
	strLog = strLog & vbTab & "Error Number :-  " & CSTR(Err.Number) & vbCrLf
	strLog = strLog & vbTab & "Error Description :-   " & CSTR(Err.Description) & vbCrLf
	
	wscript.echo strLog
	
	WScript.Echo vbTab & "Press [ENTER] to continue..."

	' Read dummy input. This call will not return until [ENTER] is pressed.
	WScript.StdIn.ReadLine
	
	WScript.Quit 

end if
 
Dim objFields
Dim iFieldCount
Dim iFieldID

'Define recordset and SQL query
Set objRS = objConn.execute("SELECT * FROM Addresses")
 
Set objFields  = Nothing
 
'While loop, loops through all available results
DO WHILE NOT objRS.EOF

	if (objFields is Nothing) Then
	
		Set objFields = objRS.Fields  

		iFieldCount = objFields.Count
		
	End if	
	
	strRow = ""
	
	'add data delimited by Tabs
	strRow = objRS.Fields("AddressID") & "" _
				& vbTab & objRS.Fields("FirstName") & "" _
				& vbTab & objRS.Fields("LastName") & "" _				
				& vbCrLf

	strData = strData & strRow
	
	'move to next result before looping again
	'this is important
	
	objRS.MoveNext
	'continue loop
	
Loop
 
'Close connection and release objects
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
 
'Return Results via MsgBox
MsgBox strData

Invoke


C:\Windows\SysWOW64\cscript.exe getDataMSAccess.vbs

Output

Output – Good

Output – Failure

Error Description :- Provider cannot be found. It may not be properly installed
Error Number :- 3706

 

Source Control

GitHub

Posted to GitHub here

 

Things to keep in Mind

OLE-DB Provider

As the OLE-DB Provider is 32-bit, on a 64-bit platform force script to run in 32-bit mode by explicitly referencing C:\Windows\SysWOW64\cscript.exe.

Syntax

C:\Windows\SysWOW64\cscript.exe [vbScriptFi<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>le]

Sample Invocation

C:\Windows\SysWOW64\cscript.exe getDataMSAccess.vbs

Office365 – Email Connectivity Testing through Mozilla Thunderbird

Background

Earlier this weekend got an email that analysts were no longer receiving emails that we had subscribed them to via Microsoft Reporting Services.

Troubleshooting

Log Files

Checked the various log files ( Reporting Services, IIS SMTP).

BTW, we are using IIS SMTP as a Smarthost Relay.

The logs were not illuminating.

 

Mozilla Thunderbird

Tried the same configuration with slight variations.  Actually many more times than I can remember.  And, definitely much more than I will admit.

 

Google

That chick named Google is your friend and so looked for how to configure email clients to use Office 365.

Found some good leads, but nothing worked out.

Configuration

Process

Here is the process we undertook to configure Mozilla Thunderbird to use Microsoft Office 365.

  1. Launch Mozilla Thunderbird
  2. Choose the Account
    • Right click on the Account you have selected
    • And, from the dropdown menu, choose “Settings
  3. Account Settings
    • The “Account Settings” window appears
    • At the bottom of the “Account Settings” window we will observe the “Account Actions” panel
    • One of the choices available in the “Account Actions” panel is the “Add Mail Account” option
    • Please choose the “Add Mail Account” option
  4. “Mail Account” Setup
    • The “Mail Account Setup” window appears
    • Initial Screen
      • It is prefilled with your current system’s full name
      • Please enter your full email address
      • And, password
      • Once entered, please press the Continue button
    • Suggested Configuration
      • Based on the email address entered, Mozilla retrieves the domain name
      • The registered DNS Provider for the domain name is contacted
      • And, asked for MX record
      • Using the MX record, the servers registered for Mail are then contacted
      • If the Mail providers are able to provide mail registration data, communication is started with them
    • Manual Configuration
      • Please click on the manual config button to review or adjust configuration data

Images

Mail Account Setup
Access Account Configuration

Account Settings

Account Settings

Mail Account Setup – 01

Mail Account Setup – 02

Mail Account Setup – Looking up Configuration – Email Provider

Mail Account Setup – Looking up Configuration – Email Provider – Configuration found in Mozilla ISP database

Mail Account Setup – Manual Configuration

Configuration Results

Flow Protocol Server hostname Port SSL Authentication
Incoming IMAP imap-mail.outlook.com 993 SSL/TLS Normal password
Outgoing SMTP smtp-mail.outlook.com 587 STARTTLS Normal password

 

Network Monitoring

Microsoft Network Monitor

Once we were satisfied that we were able to establish communication between our host and the Mail Server, we used Microsoft Network Monitor to review the traffic.

Here is the filters we employed and what we captured via “Microsoft Network Monitor“.

Filter

Textual

ProcessName.Contains(“thunder”)

Image

Network Conversations

 

References

  1. Microsoft
    • Technet
      • Network Monitor Conversation Filtering
        Link

Summary

A good solution was so far away until we discovered that Mozilla Thunderbird is able to communicate with the Provider and request configuration data.