Excel 2007 Notes

Explore the Ribbon

2007-06-01T00:26:00.000-07:00

Wondering where your favorite Excel 2003 commands are located in the new Excel 2007 interface? Or just want to explore the rich, new design with a little guidance?

View this useful video tutorial here: http://office.microsoft.com/en-us/excel/HA101491511033.aspx

No Application.Filesearch in 2007

2007-05-09T00:51:00.000-07:00

In earlier versions Aplication.FileSearch could be used to loop through all the files contained in a Directory. This does not appear to work in 2007 versions. I have managed to use Dir to achieve this instead

--------------------------------------------------------------------------------------' Module : Module1
' DateTime : 09/05/2007 08:43
' Author : Roy Cox (royUK)' Website : www.excel-it.com for more examples and Excel Consulting
' Purpose : Open all worksheets in a specific folder' Disclaimer; This code is offered as is with no guarantees. You may use it in your' projects but please leave this header intact.
'---------------------------------------------------------------------------------------
Option Explicit
Sub Open_All_Files()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String

sPath = "C:\Documents and Settings\Roy Cox\My Documents\" 'location of files
ChDir sPath
sFil = Dir("*.xlsx") 'change or add formats
Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

' do something
oWbk.Close True 'close the workbook, saving changes
sFil = Dir
Loop ' End of LOOP
End Sub

The Macro Recorder in Excel 2007

2007-05-07T08:56:00.000-07:00

Some users are under the impression that the macro recorder is no longer available in the 2007 version of Excel. This is not so. It is accessed from the Developer Tab, however the Developer Tab is hidden in a default installation and contains all the controls for recording macros, activating the VBA editor (VBE), using controls on the worksheet, xml data and the document panel. You can switch this on or off with a setting in Excel Options... (Office Button>Excel Options>Popular Tab and check 'Show Developer tab in the Ribbon'

As with the other Tabs the Developer Tab is divided into Groups of Controls. Hover your Mouse over a Control to display a brief description of the Control. As you will see the Record macros is in the Controls Group.

2007-05-07T06:50:00.000-07:00

Creating a template in Excel 2007

Because of the new File Formats introduced with Microsoft Office 2007 you need to select the correct type of Workbook to save as in Excel 2007. This applies to creating an Excel 2007 Template. When you have finished designing your Workbook. follow these steps to save your new file as an Excel 2007 Template: Click on the Office Button (see image above) to open the drop down menu

Choose the Save As option

Choose the Other formats option

Choose the Save As option to open the Save As dialog box

Click on the Save as type option to open the drop down list

Scroll through the list to find the template options

Template options in Excel 2007
There are three templates that can be created in Excel 2007:
· Excel Template (*.xltx)
· Excel Macro Enabled Template (*.xltm)
· Excel 97 - 2003 Template (*.xlt)