Tuesday, February 16, 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
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)
MsgBox "I have Run!"
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
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
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
' Run the Macro without Argument
oXL.Application.Run "'CanBeDeleted.xlsm'!Store_Value", sPName
' Save and Close the Workbook
' Quit the Excel
' Release Objects - Good Practive
If Not oWB Is Nothing Then Set oWB = Nothing
If Not oXL Is Nothing Then Set oXL = Nothing
If Err <> 0 Then
The macro saves and closes the workbook and quits Excel
Execute a macro in a different workbook
Run a Automatic Macro in Word Document
Saturday, February 13, 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:
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 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.
Application.DisplayCommentIndicator = xlCommentAndIndicator
Just in case you feel the sheet is littered with comments you can turn it off by using
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
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 2007 and above
Maximum No of Rows
Maximum No of Columns