You are here: Home / Archives for Workbook

Excel 2010 VBA objects: Handling worksheets

August 26, 2012 By

In a previous post we discussed how to handle workbooks through VBA code. In this post we will go one step furher to deal with worksheets.

To add a new worksheet:

ActiveWorkbook.Worksheets.Add

To activate a worksheet:

ActiveWorkbook.Worksheets(1).Activate   ‘specify worksheet by index number
ActiveWorkbook.Worksheets(“sheet12″).Activate   ‘specify worksheet by name

To delete a worksheet:

ActiveWorkbook.Worksheets(“sheet8″).Delete    ‘delete worksheet by name
ActiveWorkbook.ActiveSheet.Delete    ‘delete the active worksheet

To get the name of the active worksheet

ActiveWorkbook.ActiveSheet.Name

To print preview a worksheet

ActiveWorkbook.Worksheets(“sheet5″).PrintPreview

In the following example the ListWorksheets macro will loop through all the worksheets in the active workbook to retrieve their names and then, at the end of the loop, list all the names in a message box.

Sub ListWorksheets()
For i = 1 To ActiveWorkbook.Worksheets.Count
strNames = strNames & Chr(13) & ActiveWorkbook.Worksheets(i).Name
Next i
MsgBox strNames
End Sub

The next example, which you can find in the download page of this site, prints the hidden sheets in a workbook:

Sub PrintHiddenSheets()
‘*********************************************************
‘ Print only hidden sheets in the active workbook *
‘*********************************************************
Dim wSheet As Worksheet
Dim CurStat As VariantFor Each wSheet In ActiveWorkbook.Worksheets
If Not wSheet.Visible Then
CurStat = wSheet.Visible
wSheet.Visible = xlSheetVisible
wSheet.PrintOut
wSheet.Visible = CurStat
End If
Next
End Sub

Filed Under: Excel 2007, Excel 2010, Visual Basic Tagged With: , , , ,

Excel 2010 VBA objects: Handling workbooks

August 12, 2012 By

In a previous post I gave an introduction about VBA objects. In this post I will zoom a little bit into objects related to the workbooks. Remember that these posts are not intended to teach programming, but will rather give some highlights on the subject.

Let us start with an example:

Suppose we want to open a workbook called “Test.xlsx”, Get the workbook name with full path, find out how many worksheets are in the workbook and then close the workbook. We will use the following VBA code:

SUB ProcessWorkBook
Workbooks.Open Filename:=”Test.xlsx”   ‘open the  workbook
‘Once the workbook is open, we will use the ActiveWorkbook property of the Application object
strPath = ActiveWorkbook.FullName  ‘Get workbook name with full path
intSheets= ActiveWorkbook.Worksheets.Count  ‘get the count of worksheets in this workbook
Workbooks(“Test.xlsx”).Close  ‘close the  workbook
Msgbox strPath     ‘ Display name and path
Msgbox intSheets  ‘Display No. of worksheets
END SUB

Other methods that you can use:

‘To open a workbook as read only:
Workbooks.Open Filename:=”Test.xlsx”, ReadOnly:=True
‘To create a new workbook
Workbooks.Add

Once the workbook is open, then you can access the ActiveWorkbook property. There are a number of variables that you can retrieve e.g.

ActiveWorkbook.Name ‘Get workbook name
ActiveWorkbook.Path ‘Get workbook full path

You can also perform the following actions:

ActiveWorkbook.PrintPreview ‘print preview the workbook
ActiveWorkbook.SaveAs ‘save workbook with different name
ActiveWorkbook.Close ‘close the workbook

 

Filed Under: Excel 2007, Excel 2010, Visual Basic Tagged With: , , , ,

Save an Excel 2007 workbook to Excel 97-2003 format

October 7, 2009 By

To save an Excel 2007 workbook to Excel 97-2003 format follow these simple steps:

1. In Excel 2007, open the workbook that you want to save in Excel 97-2003 format.

2. Click the Microsoft Office Button XL7Office and point to the arrow next to Save As.

3. Under Save a copy of the document, click Excel 97-2003 Workbook.

The file will be saved in the Excel 97 – Excel 2003 file format. This will make it possible to open the file in versions of Excel 97 through Excel 2003.

XL7to2003

Filed Under: Excel 2007, General Tagged With: , ,

How to find links in a workbook

February 13, 2009 By

There is no automatic way to find links used in a workbook. However links use brackets [ ] to enclose the source workbook name, and it also uses the exclamation mark at the end of the sheet name in the link. So you can search for one of these two symbols depending on what type of links you are looking for.

If you are looking for external links only, use the ‘[' . If you are looking for all links, external links and links within the same workbook, then use '!'.

Examples of links:

1. =Sheet2!$C$1
2. =[Book2]Sheet1!$C$1

Link 1 above is a link to a worksheet within the same workbook, while link 2 is a link to an external workbook.

Steps to find a link:

1. On the Edit menu, click Find. the Find and replace window will open.
2. Click Options to expand the window.
3. In the Find what box, enter “!”.
4. In the Within box, select Workbook.
5. In the Look In box, select Formulas.
6. Click Find All.
7. Your links will be listed in the box at the bottom of the Find and replace window.
8. To select the cell with a link, select the line that contains the link.

findlink1

Applies to Excel 2003

Filed Under: Links Tagged With: , ,

A macro to merge worksheets from two workbooks into a new workbook

January 3, 2009 By

If you have two workbooks, or more, and you want to merge or consolidate those workbooks into one, then you may find it a tedious task to move the worksheets manually.

The following macro reads two workbooks and copy all the worksheets into a new consolidated workbook. If you have more than two workbooks as input then you can modify the macro accordingly.

Sub MergeWorkbooks()
‘**********************************************************
‘ Merge worksheets from two workbooks into a new workbook *
‘**********************************************************
Dim OldBook1 As Workbook
Dim OldBook2 As Workbook
Dim NewBook As Workbook
Dim wSheet As Worksheet
‘ Stop screen flicker and speed up the execution of the macro
Application.ScreenUpdating = False
‘ Create a new workbook ( the destination for merging the old ones)
Workbooks.Add
Set NewBook = ActiveWorkbook
‘ Assign the old workbooks (must be open before running the macro)
Set OldBook1 = Workbooks(“TestBook1.xls”) ‘ Put the required file name here
Set OldBook2 = Workbooks(“TestBook2.xls”) ‘ Put the required file name here
‘ Loop thru the first workbook an copy its worksheets to the destination workbook
For Each wSheet In OldBook1.Worksheets
wSheet.Copy After:=NewBook.Worksheets(NewBook.Worksheets.Count)
Next
‘ Loop thru the second workbook an copy its worksheets to the destination workbook
For Each wSheet In OldBook2.Worksheets
wSheet.Copy After:=NewBook.Worksheets(NewBook.Worksheets.Count)
Next
‘ Save the destination workbook
NewBook.SaveAs Filename:=”C:/TestMerg2.xls” ‘ Put the required path and file name here
‘Set screen updating back to normal
Application.ScreenUpdating = True
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Suggested Post:
How to write a VBA macro

Applies to: Excel 2003

Filed Under: General, Visual Basic Tagged With: , , , ,

A macro to print only hidden sheets in a workbook

November 25, 2008 By

If you have a workbook with a number of worksheets, some visible and other hidden, and you want to print only the hidden sheets, then you can use the macro shown below.
Because Excel will not allow the macro to be printed while it is hidden, this macro will make the sheet visible, print it and then hide it again.

Sub PrintHiddenSheets()
‘*********************************************************
‘ Print only hidden sheets in the active workbook *
‘*********************************************************
Dim wSheet As Worksheet
Dim CurStat As Variant
For Each wSheet In ActiveWorkbook.Worksheets
If Not wSheet.Visible Then
CurStat = wSheet.Visible
wSheet.Visible = xlSheetVisible
wSheet.PrintOut
wSheet.Visible = CurStat
End If
Next
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Check this post if you want to know How to write a VBA macro.

Filed Under: Printing, Visual Basic Tagged With: , ,

A macro to print only visible sheets in a workbook

November 11, 2008 By

If you have a workbook with a considerable number of worksheets, some visible and other hidden, and you want to print only the visible sheets, then you can use this macro:

Sub PrintVisibleSheets()
‘***************************************************
‘Print only visible sheets in the active workbook *
‘***************************************************
Dim wSheet As Worksheet
For Each wSheet In ActiveWorkbook.Worksheets
If wSheet.Visible Then wSheet.PrintOut
Next
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Check this post if you want to know How to write a VBA macro.

Filed Under: Printing, Visual Basic Tagged With: , , ,

How to print all sheets in a workbook

November 8, 2008 By

To print all worksheets in a workbook then :

1. In the File menu click Print.

2. The Print window will open.

3. At the bottom of the Print window find a section labeled: Print what .

4. Click on the radio button labeled: Entire workbook.

5. Click OK.

Applies to Excel 2003

Filed Under: Printing Tagged With: , , ,

How to copy or move worksheets within the same workbook

November 7, 2008 By

To copy or move worksheets within the same workbook follow these steps:

1. Switch to the workbook which has the sheet to be copied or moved.

2. in the Edit menu click Move or copy sheet.

3. The Move or copy dialogue will open.

4. The To book drop down will default to the current workbook. Leave this as it is.

5. in the Before sheet list click where you want the sheet to be moved (position within the workbook).

6. Select the Create a copy check box if you want to retain the original worksheet , or deselect it if you just want to move the worksheet to a new location withinthe workbook.

Applies to Excel 2003


Filed Under: General Tagged With: , , ,

How to print more than one sheet in a workbook

November 4, 2008 By

To print more than one worksheet in a workbook then :

1. Select the worksheets you want to print.

  1. If the sheets to be printed are adjacent then click on the first one, press SHIFT then click the last one.
  2. If they are not then click on the first one, press CTRL then click the others one at a time.

2. In the File menu click Print.

3. The Print window will open.

4. At the bottom of the Print window find a section labeled: Print what .

5. Click on the radio button labeled: Active sheet(s) .

6. Click OK.

Applies to Excel 2003

Filed Under: Printing Tagged With: , ,
« Older Posts