Tuesday, February 16, 2010

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

Monday, February 15, 2010

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

Excel VBA and Power Point VBAPowerpoint VBE Screen

EXcel VBA and Powerpoint VBAExcel 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

Saturday, February 13, 2010

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

Office 2010 - Application.FileSearch ErrorRun-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

Saturday, February 06, 2010

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

Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group