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

Sharepoint – Export List – Error – “To export a list, you must have a Microsoft SharePoint Foundation-compatible application”

Background

Trying to export a SharePoint List, but getting the prompt and error pasted below.

Prompt

Do you want to open or save owssvr.iqy from sp..

Textual

Do you want to open or save owssvr.iqy from sp..

Image

Error

Error – SharePoint Foundation-Compatible Application

Textual

To export a list, you must have a Microsoft SharePoint Foundation-compatible application.

Image

 

TroubleShooting

Microsoft SharePoint Foundation Support

Is “Microsoft SharePoint Foundation Support” installed?

Accessed “Control Panel \ “Add or Remove Programs” \ “Microsoft Office Professional Plus 2010” \ Changed and made sure that “Microsoft SharePoint Foundation Support” is checked

 

Microsoft Office

Check MS Office Version #

MS Word

Using MS Word Help/About, check Version#

Image

Version Info:

Product :- Microsoft Office Professional Plus 2010
Version # :- 14.0.7184.5000 ( 32-bit)

Version Matrix

Image

Tabulate
Product Version Version# – Low Version# – High
Office 2010 – RTM 14.0.4763.1000 14.0.6029.1000
Office 2010 – SP1 14.0.6029.1000 14.0.7015.1000
Office 2010 – SP2 14.0.7015.1000

 

Explanation

Our current version # is 14.0.7184.5000.

And, SP2 is at minimum 14.0.7015.1000.

 

Microsoft Office – Repair

Image

Control Panel \ All Control Panel Items \ Programs and Features – Uninstall or Change

Microsoft Office Professional Plus 2010 – Change your Installation of Microsoft Office Professional Plus 2010

Microsoft Office Professional Plus 2010 – Configuration Progress

Repairing Microsoft Office Professional Plus 2010 ….

Microsoft Office Professional Plus 2010 – Configuration Complete

The configuration for Microsoft Office Professional Plus 2010 is complete.
To make your changes take effect, exit and restart and open Office Programs.

Microsoft Office Professional Plus 2010 – Configuration Complete

In order to complete setup, a system reboot is necessary.
Would you like to reboot now?

Validation

Post system reboot, launch Internet Explorer and revisited SharePoint List

Images

Microsoft Excel Security Notice

Microsoft Office Has identified a potential security concern

Textual

Microsoft Office Has identified a potential security concern.

Image

Excel

Exported List

Summary

To address the error “To export a list, you must have a Microsoft SharePoint Foundation-compatible application“, we were getting we simply repaired our microsoft Office 2010 installation.

In some cases one needs to apply the latest Service Pack, but we verified that we are already on the latest SP.

 

References

  1. Microsoft
    • Technet
      • Kim P – MSFT
        • To export a list, you must have a Microsoft SharePoint Foundation-compatible application
          Link
    • Support
      • Description of Office 2010 Service Pack 2
        Link

Google Drive – Google Sheets – Pasting Tables

 

Background

We all take for granted how seamlessly Microsoft Products work together.

Take for instance, I use Microsoft SQL Server Management Studio to run a query and I will like to prepare a document from the query’s result.

I simply copy the Output Grid’s content, launch Microsoft Word or Excel, and pasted it.

Through the magic of OLE or whatever they call it these days, the data is well received and formatted in the receiving Office App.

 

Google Drive

These days my main sharing tool is WordPress for public consumption and Google Docs for private data.

 

Here is the genesis of our Problem

SQL Server Management Studio ( SSMS )

Grid

Here is output of a query I ran in SSSM

ssms

Explanation

  1. Nice and visually appealing for an Engineer

 

Google Drive – Google Docs

Here is what things look like when pasted into Google Docs..

copiedintogoogledocs-20170120-1031am

 

What to do

  1. Looked for Convert to Table
  2. Insert Text as table
  3. Import Text

 

Nothing helpful.

 

Solution – 01

Google Drive – Google Sheets

Created a new file, rather than Document went with Sheets

copiedintogooglesheets-2017012-1039am

 

Explanation

  1. Making progress
    • Kept the grid or columns paradigm
    • That is things are not jumbled well, with text intertwined together

 

Google Drive – Copy From Google Sheets Into Google Docs

Here we copied the cells from Google Sheets Into our original Google Docs

copiedfromgooglesheetintodocs-20170120-1044am

 

Explanation

  1. Thankfully things are kept neatly arranged in a Columns
  2. Need to get rid of some extra columns and that is doable

 

Google Drive – Google Docs – Table – Delete Extra Columns

Got rid of the extra columns using the menu item Table / Delete Columns.

Steps

  1. Inside the Google Doc
  2. Select the extra columns
  3. And, use menu item Table / Delete Columns

 

googledocs-columnsdeleted-20170120-1053am

Format the Table

One of the great things about these Google Products such as Chrome and Google Drive is that they are extensible and have such a nice and rich 3rd party ecosystem.

 

Adds On

Table Formatter

I already have Table Formatted installed and so let us initiate it by accessing Google Docs menu items “Add-ons” \ “Table Formatter”.

 

Google Docs – Menu – Add-Ons

addson

 

Add-On – Table Formatter – Default Templates

Here are some of the Default Templates available

tableformatter-20170120-1057am

Customize Table with Add-On – Table Formatter

Select the Google Docs’s tale and choose the one of the Formatting Choices.

Here is our colored layout.

googledocs-tableformatted-20170120-1103am

 

Summary

  1. Copy SSMS Grid Data into Clipboard
  2. Create a new Google Sheet ou use existing one
    • Paste copied into Sheet
  3. Create a new Google Doc
    • Select data from Google Sheet
    • Copy into Clipboard
    • And, paste into Google Doc
  4. In Google Docs
    • Using 3rd Party Add Ons such as Table Formatter format Table

Solution – 02

Outline

  1. Copy SSMS Grid output into Clipboard
  2. Use Microsoft Excel
    • Launch Microsoft Excel
    • Copy Grid’s data into Excel
    • If you like the way Excel Formats Table, use Excel’s Table Formatting functionality
      • Using F8, make Sheet’s column into actual table
        • Be sure you have Column Headers and all
  3. Create or use New Google Docs
    • Copy Table’s content from MS Excel
    • Paste into Google Doc

 

In MS SSMS, Copying with Headers

ssms-copywithheaders-20170120-1126am

Explanation

 

In MS Excel, Pasted SSMS Grid

excel-pastedgrid-20170120-115am

 

In MS Excel, Create Table

Here is the panel displayed upon clicking on F8 and thus initiating the “Create Table” options

createtable-20170120-1119am

Explanation

  1. Please pay attention to the “My table has headers” option

 

In MS Excel, Formatted Table

excel-createtabled-20170120-1120am

Hotmail.com / AddIns / Boomerang

Background

Playing around with using Google Docs from iPhone this afternoon.  I really did not want to have to enter Credit Card Information to use the suggested Apps.

In the middle of that or other things, I found that a new icon is now part of the my Hotmail interface.

 

Boomerang, The Movie

Yes, I liked Eddie Murphy and Leila Rashon in the Boomerang Movie.

But, what does that have to with my hotmail.

Usage

So here is what occurs when I am reviewing emails, there is a nice lettered indicator that reads Boomerang.

 

emailmail

Uninstall Boomerang

Outline

  1. Login to Hotmail
  2. On the Top Menu Bar, click on the Mail Settings icon
  3. From the Mail Settings menu, choose Manage Integrations
  4. The Add-Ins for Outlook panel appears
    • The “All” Tab is displayed initially
    • Please choose “My Add-ins” to review the ones you have enabled
    • Turn off all unnecessary ones

 

Screen Shot

Top Menu Bar

topmenubar

 

Mail Settings Menu

mailsettings-dropdown

 

Add-Ins for Outlook

Add-Ins for Outlook – All

addinsforoutlook-all

 

Add-Ins for Outlook – My add-ins – Initial Screen

addinsforoutlook-myaddins

 

Add-Ins for Outlook – My add-ins – Boomerang – Disabled

addinsforoutlook-myaddins-boomerang-disabled

 

Microsoft – Office On Mac OS/X – Install Error – “no software found to install”

Background

One of our end users has a Mac and upon trying to access the Excel file we sent him, he is unable to as he does not have Microsoft Office installed.

 

Operating Environment

His laptop is an Apple MacBook Air.  And, he is running OS X El Capitan.

 

Error

When he tries to install Office 2008 on the OS listed above, he is getting the error message posted below.

Image

TheInstallationFailed-ThereWasNoSoftwareFoundToInstall

 

Textual


The installation failed.

The installer could not install the software.

The Installer could not install the software because there was no software found to install.

Office Version on Apple Mac OS/X

Checked wikipedia and gained quick familiarity with the versions of Office available for Mac OS/X

Version Release Date Attributes
 Office 2008 Jan 15th, 2008 Universal Binary (works on Intel and Powerpc Macs )
Office Open XML
 Office 2011 Oct 26th, 2010  Mac-based Ribbon
 Office 2016 Sept 22nd, 2015

 

 

Version Installed

Office 2011

We have an unused license for Office 2011 and went with that one.

InstallWasCompletedSuccessfully (Cropped)

 

Other’s Work

There are a couple of ideas on the Internet on how to solve this problem.

The ideas include:

  1. Copying the installer ( dmg ) from the DVD unto your computer

These ideas were fruitless to us.