How to change the Source of Pivot Table using VBA

How to configure Pivot Table source data externally through VBA

A Pivot Table is linked to a particular source data. If for some reasons, you need that to be configured by users the following code will give some hint:

Sub Change_Pivot_TableDataSource()

Dim oPT As PivotTable

Dim oPC As PivotCache

Dim ORange As Range

Set oPT = ActiveSheet.PivotTables(1)

Set oPC = oPT.PivotCache

Set ORange = Application.InputBox(Prompt:="Select the New DataRange", Type:=8)

oPC.SourceData = "Sheet1!" & Application.ConvertFormula(ORange.Address, xlA1, xlR1C1)

oPT.RefreshTable

If Not oPT Is Nothing Then Set oPT = Nothing

If Not oPC Is Nothing Then Set oPC = Nothing

End Sub

The code gets the new data range through Input Box and modifies the SourceData of the Pivot Table. Change the Sheet name accordingly before you use the code.

How to Check the Source Type of Pivot Table using VBA

Excel VBA Check Pivot Source

The following snippet could help in getting the source type of the Pivot Table

Sub CheckSourceConnection()

Dim pvtCache As PivotCache

Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1)

On Error GoTo No_Connection

If pvtCache.SourceType = xlDatabase Then

MsgBox "The data source connection is: " & _

pvtCache.SourceData, vbInformation, "Pivot Table Source"

ElseIf pvtCache.SourceType = xlExternal Then

MsgBox "The data source connection is: " & _

pvtCache.SourceDataFile, vbInformation, "Pivot Table Source"

End If

Exit Sub

No_Connection:

MsgBox "Pivot Table source cannot be determined.", vbInformation, "Pivot Table Source"

End Sub

How to Add Popup Menu Item in Excel/Word using VBA

Create Popup Menu (Right Click menu) using VBA

Here is a simple snippet that will add a menu item to the popup menu and assign a macro to it

Public Const APP_SHORTNAME = "VBADUD_POPUP"

Sub Add_To_Popup_Menu()

Dim ctlNewMenu As CommandBarControl

Dim ctlNewGroup As CommandBarControl

Dim ctlNewItem As CommandBarControl

On Error GoTo Err_Trap

On Error Resume Next

Application.CommandBars("Cell").Controls(APP_SHORTNAME).Delete

On Error GoTo 0

Set ctlNewMenu = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup)

ctlNewMenu.Caption = APP_SHORTNAME

'--- Button - Load Raw Data ------------

Set ctlNewItem = ctlNewMenu.Controls.Add(Type:=msoControlButton)

ctlNewItem.Caption = "Process Data"

ctlNewItem.OnAction = "ProcessData"

Err_Trap:

If Err <> 0 Then

Err.Clear

Resume Next

End If

End Sub

The above will create a new Group and add the “Process Data” control to it.

How to Create PDF from Word Document using VBA

Convert Word to PDF using VBA

Word 2007 has a new method - Document.ExportAsFixedFormat, which saves the document as PDF or XPS format

The following code will save the current document as PDF in the same path

Sub Convert_2_PDF()

ActiveDocument.ExportAsFixedFormat OutputFileName:= _

ActiveDocument.Path & "\" & ActiveDocument.Name & ".pdf", ExportFormat:= _

wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _

wdExportOptimizeForPrint, Range:=wdExportAllDocument, _

Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _

CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _

BitmapMissingFonts:=True, UseISO19005_1:=False

End Sub

Welcome Office 2010

At last the Office 2010 is out - here is what Microsoft has to say about its new baby:


"Microsoft® Office 2010 gives you rich and powerful new ways to deliver your best work - whether you’re at work, home, or school - on a computer, Web browser, or Smartphone. Grab your audience’s attention and inspire them with your ideas using enhanced tools, customizable templates, and photo editing capabilities. Work with multiple people from different locations at the exact same time using new co-authoring capabilities. By offering more ways to access your files from virtually anywhere, Office 2010 puts you in control. "

For more info -->
http://www.microsoft.com/office/2010/

Feel free to post your views on Office 2010

Office 2010 Downloads are available on request (https://microsoft.crgevents.com/Office2010TheMovie/Content/Default.aspx?p=Home&)

VBA Additional Contols not enabled in Tools menu

Probably a silly one, but it took some good time of one of my student. So if the 'Additional Controls' in Excel VBA/Word VBA window is not enabled

Enable the Toolbox --> View -->Toolbox and it becomes enabled

How to Create New Menu using Excel VBA

How to automatically add menu to Excel using VBa

Here is a code to add a new menu and a button whenever a sheet is opened.

Option Explicit
Private Const APPNAME As String = "Sample Menu"
Private Sub Workbook_Open()


Dim ctlNewMenu As CommandBarControl
Dim ctlNewGroup As CommandBarControl
Dim ctlNewItem As CommandBarControl
Dim vID
On Error GoTo Err_DUD

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls(APPNAME).Delete
On Error GoTo 0
Set ctlNewMenu = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
ctlNewMenu.Caption = APPNAME
'--- Add Control Buttons ---------------
Set ctlNewItem = ctlNewMenu.Controls.Add(Type:=msoControlButton)
ctlNewItem.Caption = "Sample Button"
ctlNewItem.OnAction = "SayHello"
ctlNewItem.TooltipText = "Sample Button by VBADUD"
Err_DUD:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub