Professionally Embedding Excel Into Powerpoint

Embedding Excel spreadsheets into Powerpoint may sound simple, but there's a sloppy way to do it and a nice, clean, professional way to do it.

Always hide the gridlines or your presentation will look untidy and unprofessional.  See examples below.

Deleting the Pivot Cache in Excel

In order to keep your Excel file at its leanest, it's a good idea to routinely clean out the pivot cache when your data in your source changes.  If you don't, you'll notice that names in your pivot fields that are older and no longer exist in your new data still appear in your pivot field lists. 


To clear your pivot cache, run this macro: 


  Sub DeletePivotCache() 
  Dim pt As PivotTable
  Dim ws As Worksheet 
  For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
      pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
      pt.PivotCache.Refresh
    Next pt
  Next ws 
  End Sub 

Why do I get Multiple PDF files from a single Excel file?

Every now and then, when you try to print an entire Excel file to an Adobe PDF file, you'll notice that it'll start printing to multiple PDF files.  


This is because not all the worksheets in the workbook are the same dpi. 


I've noticed that 600 dpi seems to work best for all the files.  This
setting can be found in the "Page" tab of the "Page Setup" option in
the "Print Quality" prompt under the "Paper size:" prompt. 

Running Code On Cell Change in Excel

  


Here's a nice code that I found that runs a code whenever there's a
change in an Excel cell. 
I found it on the following: 


http://www.ozgrid.com/forum/showthread.php?t=68055 


  Private Sub Worksheet_Change(ByVal Target As Range)
      Dim iSect As Range
      Set iSect = Application.Intersect(Target, Range("Change"))
      If Not iSect Is Nothing Then
          Call Code
      End If
  End Sub 


Where "Code" is the macro that runs anytime there's a change in the
"Change" range. 

Linking to pivot table values

  


If you find yourself getting error messages when linking to pivot
table values, it may be because the formula is not seeing the cell as
a text, so you may get a "REF#" error.  In this case, you can replace
the cell - e.g. A1 with T(A1).  The function T() returns the text
referred to by the cell. 
So for example, if you get an error message with the formula 


=GETPIVOTDATA($A3,"PivotTable","MONTH",B$2,"YEAR",$A$1) 


where $A3 contains the variable, B$2 contains the month and $A$1
contains the year, 
You would replace it with 


=GETPIVOTDATA(T($A3),"PivotTable","MONTH",B$2,"YEAR",$A$1) 


which will put in the text for A3 so the formula can calculate.

Commenting out multiple lines in VBA

To comment out multiple lines in VBA, while in the VBA editor, go to
"View" then "Toolbars" and check "Edit". 
In the "Edit" toolbar, there's a button where you can comment a block
of data.

Getting the Last Sunday of the Week

To get the Sunday of the week that just passed, use the following in
VBA: 


    Public Function GetSunday(keyDate As Date) As Date
       GetSunday = DateSerial(Year(keyDate) _
           , Month(keyDate) _
           , Day(keyDate) - DatePart("w", keyDate) + 1)
   End Function 

Drilling down on pivot table cube items when multiple page filters are selected

  


Courtesy of Ralf_from_Europe 


Please see link to thread below.


http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/eab5da48-2335-471a-a597-817ed5e20c71/

Opening up MS Access from Excel with VBA

 

One of my clients had a project where I needed to open up an Access database from Excel with the click of a button.

Here's the code.  "database.mdb" is the name of the database you want to open.

Sub OpenDB()
Dim myDir As String 
myDir = ActiveWorkbook.Path
Shell "C:\Program Files\Microsoft Office\Office11\msaccess.exe " _
& myDir & "\database.mdb"
End Sub