Connections from Excel

MS Excel and MS Share point could be connected, as mentioned above.


Go to Data-> connections-> Properties (to check the below for the existing connection)
Definition -> Connection String:

Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="";ApplicationName=Excel;Version=12.0.0.0


Definition -> Command Text:

<LIST><VIEWGUID>{7D06904D-3F2D-46CB-8E13-692848912564}</VIEWGUID><LISTNAME>{93A8F3BD-E4A1-4CE4-87A0-FFA66E87F3A9}</LISTNAME><LISTWEB>https://teams.aexp.com/sites/roadmapandstrategy/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDER>/sites/roadmapandstrategy/Lists/Automation</ROOTFOLDER></LIST>

Pivot

-  How to check the data source of Pivot?

Ans: Go to PivotTable Tools -> Options - > Change Data source ---- this will tell you the data sources of the table

In a sheet if you have multiple pivot tables it does not mean that they have same data source. If you want to change data source, you can do it here.

- Pivot total counts getting updated on the top and bottom by default, how to get a control on it?

** Grand Totals for Rows
**Grand Totatls for columns
Ans: In PivotTable tools(in menu bar)->Design there are options for Subtotatls and GrandTotatls. Solved.


- How to add extra column to the Pivot table(calculated field)?

Select already created Pivot table, go to Options->Formulas->Calculated field

Handson

-  I have found the below in most of the places in macros, what it is:  - --- Solved

[$ClarityReporting].[Master Project Name] -

> It means that you are pulling Master Project Name details from ClarityReporting Sheet.

- 6/15/15: Regular Expression in vbscript to filter the records with the director name "Ben" in them:

Issue: I tried to use "*" - regular expression as mentioned in teh below code:

Ben= "Heermance"
ActiveSheet.Range("$A$1:$CN$25425").AutoFilter Field:=28, Criteria1:=Array( _
"Goel Vikas", "*" & Ben & "*", "Radmand Payman", "Ray Michael H", "Tiku Sripriya" _
, "Wilson Sheila K"), Operator:=xlFilterValues

But looks like regular expression does not work just like that.