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.

Hide all worksheets except Activeworksheet

Sometimes back I posted how to Unhide all the worksheets.
Here is the code to Hide all the sheets except the one which is Active...
--------------------------------------------------------
Sub Hide()
'Hide all sheets macro by Saalim
Dim wkst As Worksheet
Dim sht1 As Stringsht1 = ActiveSheet.Name
For Each wkst In Worksheets
wkst.Activate
If ActiveSheet.Name <> sht1 Then
wkst.Visible = False
End If
Next wkst
End Sub
-------------------------------------------------

Row Counter for Excel

I always wondered if there is a way to count the number of rows that contains information leaving blanks. I was able to do it with following code:

Assume that your data starts from Cell A6. Run the following code:

Sub rowCount()

Range("A6").Select

rowcounter = 6

ActiveCell.Offset(1, 0).Select

Do Until ActiveCell = ""

rowcounter = rowcounter + 1

ActiveCell.Offset(1, 0).Select

Loop

MsgBox ("There are " & rowcounter & " number of rows")

End Sub

Hope this will help

How to write amounts in Lakhs and Crores (Indian Format)?

Have you ever wondered as to how to write amounts in lakhs and crores instead of millions and billions ie 10,00,00,000 format.
Its quite simple :
Right Click the cell you want to format >> format >> Number >> Click Custome (last item on the left hand side Menu).Now type one of the below syntax formats in the "Type:" Text box >> Click OK.
Without decimal : 99,99,99,999[>=10000000]#","##","##","###;[>=100000]#","##","###;#,###
With decimal : 99,99,99,999.00[>=10000000]#","##","##","##0.00;[>=100000]#","##","##0.00;#,##0.00
And now you get amounts in Indian currency format.

INFO function

=Info() can be used to get information about the location of the file, information about the OS, excel version, about the memory status etc....

Here are some examples:
=info("directory") Path of the current directory or folder.
=info("memavail") Amount of memory available, in bytes.
=info("memused") Amount of memory being used for data.
=info("numfile") Number of active worksheets in the open workbooks.
=info("origin") Absolute A1-style reference, as text, prepended with "$A:" for Lotus 1-2-3 release 3.x compatibility. Returns the cell reference of the top and leftmost cell visible in the window, based on the current scrolling position.
=info("osversion") Current operating system version, as text.
=info("recalc") Current recalculation mode; returns "Automatic" or "Manual".
=info("release") Version of Microsoft Excel, as text.
=info("system") Name of the operating environment:
=info("totmem") Total memory available, including memory already in use, in bytes.

Where Can it be used?
=info("directory") function can be used to get the path of the file in your reports.
=info("recalc") can be used to check whether the calculation status is 'Manual' or 'Automatic'.

Now there are other ways to get most of the above information, this formula is one more option that you have.

Some Text functions

We will see some Text functions which can be very useful:
1. TEXT() :
This functions formats a number and convert it to Text. For E.g., Cell A1 is having date 1/1/2009. We can convert this date to text in whatever format we like; e.g., TEXT(A1,"MMDDYYYY). The result will be 01012009. Likewise we can convert a number to text like 100 to 100.00 (Text(A1,"0.00"). Note that you may have to specify the decimals.
2. TRIM():
Trim function is used to remove extra spaces from text. Syntax: TRIM("Hello "). Result will be "Hello".
3. LEN():
LEN function is used to know the length of a text or character. It will count the spaces too.
4. UPPER():
This function is used to convert a text to UPPER case.
5. LOWER()
This function is used to convert a text to LOWER case.

When I open a workbook it prompts me to enable macros; although there are no macros in the file. Why?

Whenever Macros are recorded in a workbook, Modules get created. These modules house the VBA Codes. So whenever Excel detects any module in a workbook it prompts you to enable macros even though there might not be any macros in he file.
Example: Sometimes we try our hand on Macros and then later delete these macros. Even though there are no Macros, Excel gives an Enable Macros prompt on opening the file because the file still houses the Module(s) (including Class Module(s) if any) which have not been removed. So if you are wondering why this popup when there are no macros? All you have to do is go to the VBA Screen (Alt+F11) right click the Module(s) and delete them; this will get rid of the Enable Macros prompt.
Note : Some people get the impression that this popup appears for macros have been used on the workbook in past. However this is not true as running macros from personal.xls or an addin or any other file will neither create VBA code (in sheets or modules) nor will it create new modules. Hence mere running of macros ona file will not result in ‘Enable Macros prompt’ when you open them.

Phantom Links

What are phantom links?
A Phantom link is a link which does not exist and hence can not be updated. Most of the phantom links arise when someone sends you a workbook which is linked to other workbooks to which you do not have access. It's called a phantom link ‘cos the reference does not exist and hence can not be updated.
It will also arise when you change file name of one of the linked workbooks. The file which contained link can not find the old file name, making the link phantom. In such case excel prompts you to update the link.

Unhide all sheets thro Macro

You would have noticed that we cannot select all the worksheets from the Unhide options. This becomes tougher when we have unhide multiple sheets. We have to choose each sheet and then unhide them. Easiest way to unhide all the sheets is to use following VBA code:
'-------VBA Code---------------
Sub Unhide()
'Unhide all sheets macro by Saalim
Dim wkst As Worksheet
For Each wkst In Worksheets
wkst.Visible = xlSheetVisible
Next wkst
End Sub
'------------Code End----------------
This way we can save a lot of time on manually unhiding the sheets.

-Saalim

Web Query

Most of the people wonder if they can download a web page to Excel worksheet. It is absolutely possible with a web query. Here are the steps to do that:
1. Data >> Import External Data >> New Web Query.
2. In the resultant window input the webpage address from which you want to update the rate and click go.
3. Once the page is loaded, Check the fields (having green arrow marks) that contain the rates and click import.
4. The data is now imported into excel.
5. Now all you have to do is refresh this query. To do this right click the area where the data has been imported and you have various options including refresh. (I usually prefer to have a macro for this...)
6. There are other settings which you can access by right clicking the data area and selecting 'Data Range Properties'.

-Saalim

Default No. of Worksheets

Do you know that we can change the default no. of worksheets in a new workbook from to any numbers?

To do this:
1. Go to Tools
2. Select Options
3. Go to General Tab
4. Here you can specify the no. of sheets for a new workbook. (default should be 3)

Hope this helps!!

-Saalim

Change in my blog contents

I have decided to upload some useful stuffs and will try to share my knowledge on MS Excel from now onwards..... :-)