Kill all running excel instances using VBScript

vbScript is very useful to handle Excel applications.
Following code should be placed in .VBS file.

SET WinShell = CreateObject("WScript.Shell")
SET oExcelKill=WinShell.Exec("taskkill /F /IM Excel.EXE")
SET oExcelKill= Nothing
SET WinShell =Nothing

Above code will kill all currently running excel applications without asking any confirmation messages.

Copy an open file through Excel VBA

To copy a open file through VBA; FileCopy statement will  not work; instead follow below steps to copy an open file

1. Go to VBA Editor

2.  Add a reference to the Microsoft Scripting Runtime (Tools -References - Microsoft Scripting Runtime)

3. Below is the sample Code 

Sub test()    
   Dim CopyOpenFile As New FileSystemObject     
   CopyOpenFile.CopyFile "c:\test\asd.xlsx", "c:\test2\asd.xlsx", True
End Sub 

c:\test\asd.xlsx - Source 

c:\test2\asd.xlsx - Destination 

True - Overwrite (False - Do not Overwrite) 

Excel Slicer for Pivot table and Grand Total: Workaround

If you create  slicer for a pivot table and you want to see details of "Grand Total"; it may give you wrong result.

e.g.

1. We have sales data as

2. And We have created Pivot table as (Without any Report Filters)

   

   Report filter:
                      None

   Column Labels:
                       Sales Person

    Values:
                      Sum of Unit sold

3. Now we insert slicer of months

4. Select Any month e.g. January

5. You can see now Grand total is 3, if you want to view details of those 3 Sales persons who sold units in January, double click on 3; but you will see all data instead of only 3 people

Excel Slicers (2010 Onwards) & Dashboad

Microsoft has introduced Slicers from Excel 2010 which is a really great feature. It is nothing but filters. It should be connected to pivot tables or data connections. Also it can be connected to multiple pivot tables (provided every pivot table has same source data). This is useful to slice same data in different views. such as Dashboards. They can be used using Excel services in SharePoint 2010 also

e.g.
Let us say we have Sales data for as Sales Person, Region, Month,Quarter and Units sold.

Requirement :
Output should have two charts

1. Sales by Month (Column Chart)
2. Sales by Region  (Pie Chart)

Both of above charts should have slice and dice facility by Sales Person and Quarter

Design:

Create Two different Pivot tables. 
1. Pivot Table 1: Structure 

   Columns: B and C
   Report filter:
                       Sales Person
                       Quarter

   Column Labels:
                       Month

    Values:
                      Sum of Unit sold

Create Pivot Chart of Above Pivot Table of type 'Column'; Hide field buttons using 'Pivot Chart Tools -> Analyse -> Field Buttons -> Hide All
Format chart as per your need.

2. Pivot Table 2: Structure 


 Columns: F & G

 Report filter:
                       Sales Person
                       Quarter

   Column Labels:
                       Region

    Values:
                      Sum of Unit sold

Create Pivot Chart of Above Pivot Table of type 'Pie'; Hide field buttons using 'Pivot Chart Tools -> Analyse -> Field Buttons -> Hide All
Format chart as per your need.




3. Insert Slicer

Select any Pivot table or Pivot Chart and add slicer
Insert -> Slicer (You must select Pivot table or Pivot Chart before inserting)

Select Field names which will be used to slice and dice

Now, you need to give connection of pivot tables to both the slicers, When you create slicer it has only one pivot table connection; to connect slicer to another pivot table right click on slicer and select 'PivotTable Connections' and select another pivot table

You can format slicers if you want by selection Slicers and then Slicer tools on Ribbon.

4. Create formulae for dynamic update of Chart Titles

Now Insert formulae next to any of the  Pivot table to display chart titles as per selection of slicer items
Formula in D3 will depict which SalesPersons  are selected
Formula in D4 will depict which Quarters  are selected
Formula in D5 will depict which Quarter and SalesPersons  are selected

Assign D5 value to chart title.: Select Chart Title then go to Formula bar and type '=' and the select D5 cell

Press Enter button Repeat this step to other chart as well





5. Finalize Dashboard

Move Charts & Slicer to Column I, Format Charts and Hide Columns where Pivot tables are available i.e. Hide Columns A to G, Hide Gridlines and Headings

 You can select any Slicer items and both the Charts will change accordingly; You can select multiple slicer items by using ctrl + click.

5. SharePoint 2010 Integration

Upload Excel file in SharePoint library and right click on it to select 'View in a browser' option. Slicers can be used through SharePoint as well :)