How to clean Office Solution - .NET

How to clean Office Solution from Visual Studio / How to unistall Excel/Word addins using Visual Studio

Use the clean option in Build Menu to remove the Addin

Enable Developer Tab in Office 2010

How to enable Developer Tab in Office 2010

If the developer tab is not showing on your Ribbon UI, you can enable it from Application Options-->Customize Ribbon

Run Excel Macro from Powerpoint VBA

How to Run an Excel Macro from PowerPoint

Before writing code for doing it you need to add Excel Library to the PowerPoint VBE Project

Powerpoint VBE Screen

Excel Library in the References

This can be done from Powerpoint VBE-->Tools-->References -->Browse for the particular reference and add them.

We have the Excel macros embedded in a workbook (CanBeDeleted.xlsm)

Sub AnotherWrkBook_Macro()

MsgBox "I have Run!"


End Sub

Above code is a simple message box. The code below, however, accepts an argument and stores the same in the workbook

Function Store_Value(ByVal sPPTName As String)

Sheet1.Range("A2").Value = sPPTName


End Function

The following Powerpoint VBA code uses Application.Run method of Excel VBA to execute a particular macro.

Multiple arguments can be passed to Application.Run method

Sub Run_Excel_Macro_From_PPT()

Dim oXL As Excel.Application            ' Excel Application Object
Dim oWB As Excel.Workbook               ' Excel Workbook Object
Dim sPName As String                    ' Variable - Active Presentation Name

On Error GoTo Err_PPXL

' -----------------------------------------------------------
' coded by Shasur for http://vbadud.blogspot.com
' -----------------------------------------------------------

Set oXL = New Excel.Application
Set oWB = oXL.Workbooks.Open("C:\Users\comp\Documents\CanBeDeleted.xlsm")

' Set Excel as Visibile - Turn Off if not needed
oXL.Visible = True

' Pass and Argument
sPName = ActivePresentation.Name

' Run the Macro without Argument
oXL.Application.Run "'CanBeDeleted.xlsm'!AnotherWrkBook_Macro"

' Run the Macro without Argument
oXL.Application.Run "'CanBeDeleted.xlsm'!Store_Value", sPName

' Save and Close the Workbook
oWB.Save
oWB.Close (False)


' Quit the Excel
oXL.Quit


' Release Objects - Good Practive
If Not oWB Is Nothing Then Set oWB = Nothing
If Not oXL Is Nothing Then Set oXL = Nothing


Err_PPXL:
If Err <> 0 Then
    MsgBox Err.Description
    Err.Clear
End If
End Sub

The macro saves and closes the workbook and quits Excel

See also:


Execute a macro in a different workbook

Run a Automatic Macro in Word Document

Office 2010 - Application.FileSearch Error

Application.FileSearch doesn't work in Excel 2010 (Office 2010)

Application.FileSearch didn't work in Office 2007 (It has been deprecated from Office 2007) and hence it doesn't work in Office 2010 either. It will throw Run-time Errror 445 Object doesn't support this action

Run-time Errror 445 Object doesn't support this action

There are some good work-arounds for this:

1. FileSystemObject

2. Dir Function


For a lively discussion please have a look at http://social.msdn.microsoft.com/Forums/en/isvvba/thread/a450830d-4fc3-4f4e-aee2-03f7994369d6



Excel 2010 Application.FileSearch Error, Excel 2007 Application.FileSearch Error

How to check compatibility issues in a Word Document

How to check compatibility issues in an Office Document

Microsoft Office is getting polished rapidly. Upgrades from 2003 to 2010 saw sea change in functionality. If you are using 2010 and sending it to your friend who hasn’t upgraded, It is better to do a compatibility check

A check mark appears next to the name of the mode that the document is in.

1. Click the File tab.

2. Click Info.

3. In the Prepare for Sharing section, click Check for Issues, and then click Check Compatibility.

4. Click Select versions to show.

Show All Comments using Excel VBA / Hide All Comments using Excel VBA

The following code is a easier way to show all comments in the Excel Spreadsheet. This comes handy when you want to view all the comments to make some decisions.

Application.DisplayCommentIndicator = xlCommentAndIndicator

Just in case you feel the sheet is littered with comments you can turn it off by using

Application.DisplayCommentIndicator = xlCommentIndicatorOnly

Maximum Limit of Rows, Columns etc in Excel

How BIG is Excel 2007 and 2010

If you have worked on a large set of data from a non-Excel data source, for example, MS ACCESS, there are chances that you would have stored that in multiple sheets.

This riducules the data management. Now in Excel 2007 and above you have a big Excel workbook with 16384 columns and 10,48,576 rows

Following table gives you how big Excel has grown :)

	Excel 2003	Excel 2007 and above
Maximum No of Rows	65,536	10,48,576
Maximum No of Columns	256	16,384

Word VBA – Avoid “There are too many spelling or grammatical errors in Document ...” message

When you are automating something in Word using VBA, the below message can be a nemesis. This message will wait for user input whereas you would have though the program will continue without user intervention.

To stop this message set the Grammar checked to be true



Sub Set_Spell_Grammar()



ActiveDocument.GrammarChecked = True

ActiveDocument.SpellingChecked = True

End Sub

How to export Word Range as RTF using VBA

Save Selected Word Range as RTF using Word VBA

The following snippet saves the selected Range as RTF using VBA (Word 2007)

Sub Export_Selection_As_RTF()

Selection.Range.ExportFragment "C:\Users\comp\AppData\Local\Temp\Sample.rtf", wdFormatRTF

End Sub

See also:

Save RTF document as word

Visual Basic Common Dialog

How to add Format Conditions using Excel VBA

Conditional Formatting using Excel VBA

Here is a small snippet for FormatConditions.

Sub Format_Condition_Example()

Dim oFc As FormatCondition

Dim oRange As Range

Set oRange = Range("B2:B5")

Set oFc = oRange.FormatConditions.Add(xlCellValue, xlLess, "0.5")

oFc.Interior.ColorIndex = 3

Set oFc = oRange.FormatConditions.Add(xlCellValue, xlBetween, "0.5", "0.80")

oFc.Interior.ColorIndex = 6

Set oFc = oRange.FormatConditions.Add(xlCellValue, xlGreater, "0.80")

oFc.Interior.ColorIndex = 4

End Sub

Excel Formatting Conditions

See also:

How to Search a specific Colored Text (Range) using Excel VBA

Format ListColumns using VBA

How to Hide the PivotTable Fields List using VBA

Excel VBA - Hide Pivot Table Fields List

If you are developing some pivot table as part of the report and feel the Pivot Fields list at the right corner is bit distracting, you can turn it off as shown below:

Sub Hide_PivotTable_Fields()

ActiveWorkbook.ShowPivotTableFieldList = False 'Dont Show the Pivot Table List

End Sub

Pivot Table Fields List


Pivot Table without Fields List

Update Word Document with Excel Information using VBA

Excel Range to Word Template using VBA

Most often we maintain list of contacts in Excel workbook and it needs to be transferred to Word document (made from some template). Here is a simple snippet that can help:

The code is used to copy the content from Excel range shown below to a Word document:

Name	ContactNo	Address	Email
Christina	516 418 1234	Cincinatti	Christina@vbadud.com
Girish Kutty	516 418 6752	Cincinatti	gkutty@vbadud.com
Ravichand Koneru	777 213 213	Boston	rkoneru@vbadud.com

Sub CopY_Data_To_Word()

Dim oWA As Word.Application

Dim oWD As Word.Document

Set oWA = New Word.Application

Set oWD = oWA.Documents.Add("C:\Users\comp\Documents\Doc2.dot") ' Replace with your template here

For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row

oWD.Bookmarks("Name").Range.Text = Cells(i1, 1)

oWD.Bookmarks("ContactNo").Range.Text = Cells(i1, 2)

oWD.Bookmarks("Address").Range.Text = Cells(i1, 3)

oWD.Bookmarks("Email").Range.Text = Cells(i1, 4)

'Code for saving the document

Next i1

' Releasing objects etc

End Sub

Bookmarks are added to the Word template and whenever a new document is created from the template, the document has those bookmarks.

The code above places the information from the Excel sheet to the specific Bookmark ranges

Excel to Word using VBA

How to Login to Website Using VBA

Login to Google Account using VBA


Here is a simple code that will login to Google accounts with the provided user-name and password.



The program requires references to the following:

1 Microsoft Internet Controls
2. Microsoft HTML Object Library

Microsoft HTML Object Library- VBA Reference

Microsoft Internet Controls - VBA Reference



The Internet control is used to browse the webpage and the HTML Objects are used to identify the username and password textboxes and submit the text using the control button.


Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_2_Website()

Dim oHTML_Element As IHTMLElement
Dim sURL As String

On Error GoTo Err_Clear
sURL = "https://www.google.com/accounts/Login"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True

Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE

Set HTMLDoc = oBrowser.Document

HTMLDoc.all.Email.Value = "sample@vbadud.com"
HTMLDoc.all.passwd.Value = "*****"

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next

' oBrowser.Refresh ' Refresh If Needed
Err_Clear:
If Err <> 0 Then
Debug.Assert Err = 0
Err.Clear
Resume Next
End If
End Sub






See Also : http://vbadud.blogspot.com/2008/05/google-search-using-vba.html

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