Customize the Task Pane
February 8, 2010 • JP • No Comments • Rate This Article
• Links to this article
Using the NewFile Object in VBA we can customize the Task Pane in Excel, Access, FrontPage, PowerPoint and Word 2003 to add your file templates and frequently used files to this handy workspace. Use this as part of an add-in install, to add your custom workbooks to the task pane so they can be opened by your users anytime!
If you don't already have it, you need to make sure your VBA IDE has a reference to the appropriate Office Object Library:
Microsoft Office 11.0 Object Library
C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
Of course, adjust the reference as necessary for your Office version (11.0 = Office 2003,12.0 = Office 2007 and so on).
Per the documentation for the NewFile Object, we use the Add Method for each object to add a new document to the task pane. Unfortunately, each applicable program has its own property:
Access: Application.NewFileTaskPane.Add
Excel: Application.NewWorkbook.Add
FrontPage: Application.NewPageOrWeb.Add
PowerPoint: Application.NewPresentation.Add
Word: Application.NewDocument.Add
This means we can also add to the Task Panes of other Office programs using Automation, i.e.
Set wdApp = CreateObject("Word.Application")
wdApp.NewDocument.Add "MyFile.doc"
So if your add-in requires a custom Word document or Access DB, you can display it to your users when they open up Word or Access.
The Add Method takes three arguments:
- The path and filename of the file to be linked,
- the section where the link is to be placed, and
- the display name for the file.
For #2, we'll need to make note of the placement of our links, using the MsoFileNewSection Enumeration.
Add to Excel Task Pane
nameToDisplay As String)
Dim xl As Excel.Application
Set xl = Application
xl.newWorkbook.Add FileName:=FileName, Section:=location, displayName:=nameToDisplay
End Function
Remove from Excel Task Pane
location As Office.MsoFileNewSection, nameToDisplay As String)
Dim xl As Excel.Application
Set xl = Application
xl.newWorkbook.Remove FileName:=FileName, Section:=location, displayName:=nameToDisplay
End Function
Sample usage
Dim FileName As String
Dim fileNewSection As MsoFileNewSection
Dim displayName As String
' set up parameters for functions
FileName = "C:\My_Template.xls"
fileNewSection = msoNewfromTemplate
displayName = "My Super Duper Template"
AddToXLTaskPane FileName, fileNewSection, displayName
'RemoveFromXLTaskPane FileName, fileNewSection, displayName
End Sub
The above procedure simply calls each function and passes the needed variables to it. Now when you go to the Task Pane (press Ctrl+F1) you should see the file link in the place where you put it (in the above example, it would be found in the "Templates" section)
To add to the Task Panes for the other Office programs mentioned, we can either hard code the Office program into the code (if we know which one we're using), or write a function that will let us choose which one we want either at runtime or by calling the function with different parameters.
Custom Enum
First we'll define a custom enumeration consisting of the five applications we know we can edit.
Excel
Access
FrontPage
PowerPoint
Word
End Enum
Then we'll need a way to return a reference to whichever Office app (of the five above) is chosen. The GetApp function will take our custom enumeration as an argument and return an object reference to the Application-level object.
Return Application Object reference
Dim app As String
app = GetAppName(appName)
' if any error occurs, just continue and return Nothing
On Error Resume Next
' if the application reference being sought is actually the current
' application, set a reference to the native application object
If InStr(UCase$(Application.Name), UCase$(app)) > 0 Then
Set GetApp = Application
Exit Function
End If
' CreateObject is used exclusively because single-instance apps like
' PowerPoint can only be created once.
Set GetApp = CreateObject(app & ".Application")
End Function
Function GetAppName(appName As AppType) As String
Dim app As String
' figure out which app was chosen
Select Case appName
Case 0
app = "Excel"
Case 1
app = "Access"
Case 2
app = "FrontPage"
Case 3
app = "PowerPoint"
Case 4
app = "Word"
End Select
GetAppName = app
End Function
Now let's put it all together. We'll need a function that we can pass in the name of the app (from our custom Enum) as well as the other parameters. Unfortunately there is some hardcoding of the application names, but since the number of Office programs with Task Panes isn't likely to change, I think it's safe to hardcode in this case.
Dim obj As Object
Set obj = GetApp(app)
Select Case obj.Name
Case "Microsoft Excel"
obj.newWorkbook.Add FileName, location, nameToDisplay
Case "Microsoft Access"
obj.NewFileTaskPane.Add FileName, location, nameToDisplay
Case "Microsoft FrontPage"
obj.NewPageOrWeb.Add FileName, location, nameToDisplay
Case "Microsoft PowerPoint"
obj.NewPresentation.Add FileName, location, nameToDisplay
Case "Microsoft Word"
obj.NewDocument.Add FileName, location, nameToDisplay
End Select
End Function
A function to remove items from the Task Pane would use the Remove method but otherwise would be identical.
Sample usage
To demonstrate the use of the above function, the following procedure will add a link in the Task Pane of each of the Office apps to one of its native files.
Dim xlFileName As String
Dim pptFileName As String
Dim wdFileName As String
Dim fpFileName As String
Dim accFileName As String
Dim fileNewSection As MsoFileNewSection
Dim displayName As String
' set up parameters for functions
xlFileName = "C:\My_Template.xls"
pptFileName = "C:\My_Template.ppt"
wdFileName = "C:\My_Template.doc"
fpFileName = "C:\My_Template.fphtml"
accFileName = "C:\My_Template.mdb"
fileNewSection = msoNewfromTemplate
displayName = "My Super Duper Template"
AddToAppTaskPane Excel, xlFileName, fileNewSection, displayName
AddToAppTaskPane PowerPoint, pptFileName, fileNewSection, displayName
AddToAppTaskPane Word, wdFileName, fileNewSection, displayName
AddToAppTaskPane FrontPage, fpFileName, fileNewSection, displayName
AddToAppTaskPane Access, accFileName, fileNewSection, displayName
End Sub
Office ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Using Outlook's ItemSend Event to BCC Emails
Next Post: Finding Constant Values
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].