PowerPivot in Excel 2010

PowerPivot might be the most powerful BI tool introduced by Microsoft in recent times. I am still exploring this application. I have installed this as an Excel addin. (You can download it freely from www.powerpivot.com) But what I like about this tool is the "No restriction" in the number of rows. This makes our job easy in terms of housing the data. Earlier I used to store my data in MS Access and retrieve the required data by querying it through VBA. But now it seems I dont have to worry about the programming. Just drag and drop few things and few clicks and my job is made easy. So far this is what I have understood:

• Store millions of rows in PowerPivot
• Data can be pulled literally from any database/datasource like Oracle, DB2, Teradata, SQL, MS Access and even from Txt files and data mart like Azure.
• Create and manage relationships between these irrelative data sets. 
• You avoid writing Lookup functions after creating relationships which is 100x faster.
• PowerPivot uses Data Analysis Expression (DAX) functions similarly like Excel functions.

I will continue to use this tool in my future projects and will keep sharing my knowledge about the excellent tool. :)

Prevent Macro from running on Open Event

Some of the macros written under Auto_Open or Workbook_Open procedure will run as the file is opened.

If you have set your macro security level to low, all the files tend to open without any notification and will run the macro on Open event.

Easy way to stop this is to hold "Shift" key while opening the file. This will disable them.

If you have set your macro security level to medium, then you can stop the macro from running by clicking on "Disable Macros" option in the notification on Open.

Please note that "Disable Macros" disable all the macros in the workbook.

VBA to pull Access Data

Hi folks,

Below is the sample code written to pull data from MS Access database.

Option Explicit

Public Const sPASSWORD = "" 'Your DB Password here

Public Const sDBPath = "\Drive\DBPath" 'Your DB Path here

 

'I wrote this code to pull data from MS Access and to show up in a sheet called "Import Data"

'The code pulls data only for a particular week selected. Modify the query as per the requirement

'Some of the lines will be irrelevant but I still have them as I was testing the code with many options.

 

 

Sub importData()

Dim db As DAO.Database

Dim rs As DAO.Recordset

 

 

'New variables

Dim lRowCounter As Long 'Count rows to populate worksheet from query

Dim lastCell As Object

Dim endrow As Integer

Dim Ws As Worksheet

Dim i As Variant

Dim sWeek As String

 

'Turn off auto calculation to run the code faster

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

 

sWeek = Sheets("Admin").Range("K2").Value

 

'Clear column data

Set Ws = Sheets("ImportData")

Ws.Select

Set lastCell = Columns("A").Find("")

endrow = lastCell.Row

Range("A1:P" & endrow).Select

Selection.ClearContents

 

Range("A1").Select 'Set active cell

lRowCounter = 1

 

'Connect to DB

 

'DB exist

Set db = DBEngine.OpenDatabase(sDBPath, False, True, ";pwd=" & sPASSWORD)

'Set QueryDef = db.QueryDefs("qryReport")

'Set rs = QueryDef.OpenRecordset()

 

Set rs = db.OpenRecordset("SELECT tblUtilization.DATE_D, tblEmpDetails.E_NAME_T, tblUtilization.UNITS_N, tblUtilization.TIME_N, tblUtilization.ACCURACY_N, tblUtilization.ONTIME_N, tblUtilization.REMARKS_T, tblReports.TRANS_T, tblDate.Week_T, tblDate.Month_T FROM ((tblUtilization INNER JOIN tblEmpDetails ON tblUtilization.E_LANID_T = tblEmpDetails.E_LANID_T) INNER JOIN tblReports ON tblUtilization.REPORT_T = tblReports.REPORT_T) INNER JOIN tblDate ON tblUtilization.DATE_D = tblDate.Date_D WHERE (((tblDate.Week_T)=""" & sWeek & """))")

 

 

'This loop will collect the field names and place them in the first

'row starting at "A1."

For i = 0 To rs.Fields.Count - 1

Ws.Cells(1, i + 1).Value = rs.Fields(i).Name

Next

 

'This line simply sets the font to bold for the headers.

Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, rs.Fields.Count)).Font.Bold _

= True

 

'The next line will get the data from the recordset and copy it

'into the Worksheet (Sheet1).

 

Ws.Range("A2").CopyFromRecordset rs

 

'This next code set will just select the data region and auto-fit

'the columns

Ws.Select

Range("A1").Select

Selection.CurrentRegion.Select

Selection.Columns.AutoFit

Range("A1").Select

 

rs.Close

db.Close

Application.Calculation = xlCalculationAutomatic

 

Sheets("Report").Select

End Sub

 

Access Query from MS Excel

In Microsoft Office 97, the default path for the Northwind database is \Program Files\Microsoft Office\Office\Samples\Northwind.mdb.
A QueryDef is created in Microsoft Access and saved with the database file. It consists of a query and may or may not include criteria.
NOTE: If you receive the "User-defined type not defined" error, activate a module sheet, click References on the Tools menu, and click Microsoft DAO 3.0 Object Library check box.
Retrieving the results of a QueryDef from Microsoft Access is a five step process as follows:

1. Establish a Database object.
   
2. Establish a QueryDef object.
   
3. Establish a Recordset Object.
   
4. Retrieve the Headers (if desired).
   
5. Retrieve the data from the table.
   

After the data is retrieved, you should close all the objects that you opened by issuing .Close commands.

 

Sub GetQueryDef()

'This sub will get data from an Existing QueryDef in the Northwind

'database and place the data on sheet2.

 

Dim Db As Database

Dim Qd As QueryDef

Dim Rs As Recordset

Dim Ws As Object

Dim i As Integer

Dim Path as String

 

'Set the Path to the database. This line is useful because

'if your database is in another location, you just need to change

'it here and the Path Variable will be used throughout the code.

'

'If you're using Microsoft Office 97, the line should read:

'

'Path = "C:\Program Files\Microsoft

'Office\Office\Samples\Northwind.mdb"

'

Path = "C:\Msoffice\Access\Samples\Northwind.mdb"

 

'Set Ws

Set Ws = Sheets("Sheet1")

 

'This set of code will activate Sheet1 and clear any existing data.

'After clearing the data, it will select cell A1.

Ws.Activate

Range("A1").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range("A1").Select

 

'Set the Database and QueryDef. This QueryDef exists in the

'database.

Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True, _

Exclusive:=False)

Set Qd = Db.QueryDefs("Invoices")

 

'Create a new Recordset from the Query based on the stored

'QueryDef.

Set Rs = Qd.OpenRecordset()

 

'This loop will collect the field names and place them in the first

'row starting at "A1."

For i = 0 To Rs.Fields.Count - 1

Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name

Next

 

'This line simply sets the font to bold for the headers.

Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold _

=True

 

'The next line will get the data from the recordset and copy it

'into the Worksheet (Sheet1).

 

Ws.Range("A2").CopyFromRecordset Rs

 

'This next code set will just select the data region and auto-fit

'the columns

Sheets("Sheet1").Select

Range("A1").Select

Selection.CurrentRegion.Select

Selection.Columns.AutoFit

Range("A1").Select

 

Qd.Close

Rs.Close

Db.Close

 

End Sub

        

Source: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/ARTICLES/Q147/7/39.asp&NoWebContent=1

Comment Box Shapes and Picture

Bored with the same rectangle yellow comment boxes on your file? Here is a way to change them to shapes of your choice with picture on it (I am Using Excel 2007)

1. Select the cell with the comment
   
2. Press Shift+F2 to edit the comment box
   
3. Select any border of the comment box. You will notice that the box line has full of dots now.
   
4. Right click and select Format comment
   
5. Select "Color and Lines" tab
   
6. Now select "Fill effects" from the color drop down
   

   
   

7. Now from the Picture tab, select a picture from your local drive and add it to the comment box.
   
8. Now the background of your comment box changes to the picture you have selected
   

   
   

9. Before you proceed to change the shape of this comment box, make sure that you have "Change Shape" toolbar activated in the "Quick Access Toolbar". To do this, follow these steps:
   
   1. Select Office button on the top
      
   2. Select Excel Options
      
   3. Select Customize option and select All command from the drop down on the top of this window.
      
   4. Scroll down till you find "Change Shape" command. Add it to the Quick Access toolbar but selecting it and clicking on Add button
      

      
      

   5. Once you are done with it, you will be able to locate "Change shape" icon on the top left near the office button.
      

      
      

10. Follow Steps 1 to 3
    
11. Select a shape from the "Change Shape" options (remember which we added just now).
    
12. You will notice that the comment box shape is changed now.
    

    
    

 

Get rid of GETPIVOTDATA Option

If you want to disable the Generate GetPivotData setting, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Excel 2003

1. On the Tools menu, click Customize.
   
2. Click the Toolbars tab, and then click to select the PivotTable check box. The PivotTable toolbar is displayed.
   
3. Click Close.
   
4. On the PivotTable toolbar, click the Toolbar Options arrow. This arrow appears on the right end of the toolbar.
   
5. Click Add or Remove Buttons, click PivotTable, and then select Generate GetPivotData.
   
6. Click in the worksheet.
   
7. Click the Generate GetPivotData button that now appears on the PivotTable toolbar. When you click this button, it is turned on or off. If the button is selected, the GETPIVOTDATA formula is automatically generated.
   

Excel 2007

1. Click the Microsoft Office Button, click Excel Options, and then click the Formulas tab.
   
2. Make sure that the Use GetPivotData functions for PivotTable references check box is selected, and then click OK.
   
3. Click in the worksheet.
   
4. Click the Options tab, click Options in the PivotTable group, and then click to clear the Generate GetPivotData check box.
   

Code to export chart as Image to local drive

You can export the chart to your desired location through following code:

Sub exportChart()
Dim cht as chart
Set Cht = Worksheets("Charts").ChartObjects(1).Chart Cht.Export Filename:="C:\Temp\MyChart.gif", FilterName:="GIF"
End Sub
---------------------
I use this code to export chart and load them as image in Userform which looks pretty good for presentation.