---

Is Workbook Open/Workbook Exists/Worksheet Exists/Auto Filter/How Many Pages Printed

Got any Excel/VBA Questions? Free Excel Help

Below are Excel macros that will check to see if....

• A workbook is already open or not
• A workbook exists in a file and folder
• Open all workbooks in specified folder
• A worksheet exists in the active workbook
• A named range exists inn the active workbook
• A range is hidden by auto filers
• Count how many pages will be printed

 

Sub IsWorkBookOpen()

''''''''''''''''''''''''''''''''''''''''''

'Written by www.ozgrid.com



'Test to see if a Workbook is open.

''''''''''''''''''''''''''''''''''''''''''



Dim wBook As Workbook



	On Error Resume Next


	Set wBook = Workbooks("Personal.xls")



		If wBook Is Nothing Then 'Not open

			MsgBox "Workbook is not open", _

			vbCritical,"OzGrid.com"

			Set wBook = Nothing

			On Error GoTo 0

		Else 'It is open

			MsgBox "Yes it is open", _

			vbInformation,"OzGrid.com"

			Set wBook = Nothing

			On Error GoTo 0

		End If

End Sub

---

 

Sub DoesWorkBookExist()

'''''''''''''''''''''''''''''''

'Written by www.Ozgrid.com



'Test to see if a Workbook exists

''''''''''''''''''''''''''''''''

Dim i As Integer



	With Application.FileSearch


		.LookIn = "C:\MyDocuments"

		'* represents wildcard characters

		.FileName = "Book*.xls" 

			If .Execute > 0 Then 'Workbook exists

				MsgBox "There is a Workbook."

			Else 'There is NOt a Workbook


				MsgBox "The Workbook does not exist"

			End If

	End With

End Sub

---

 

Sub OpenAllWorkbooksInFolder()

'''''''''''''''''''''''''''''''

'Written by www.Ozgrid.com



'Open all found Workbooks in specified folder

''''''''''''''''''''''''''''''''

Dim i As Integer



	With Application.FileSearch

		.LookIn = "C:\OzGrid Dell"

		'* represents wildcard characters

		.FileType = msoFileTypeExcelWorkbooks

			If .Execute > 0 Then 'Workbook exists

				For i = 1 To .FoundFiles.Count

					Workbooks.Open (.FoundFiles(i))

				Next i

			Else 'There is NOt a Workbook

				MsgBox "The Workbook does not exist"

			End If

	End With

End Sub

---

 

Sub DoesSheetExist()

'''''''''''''''''''''''''''''''''''''

'Written by www.OzGrid.com



'Test to see if a Worksheet exists.

'''''''''''''''''''''''''''''''''''''



Dim wSheet As Worksheet



	On Error Resume Next

	Set wSheet = Sheets("Sheet1")

		If wSheet Is Nothing Then 'Doesn't exist

			MsgBox "Worksheet does not exist", _

			vbCritical,"OzGrid.com"

			Set wSheet = Nothing

			On Error GoTo 0

		Else 'Does exist

			MsgBox "Sheet 1 does exist", _

                                vbInformation,"OzGrid.com"

			Set wSheet = Nothing

			On Error GoTo 0

		End If

End Sub

---

 

Sub DoesRangeExist()

''''''''''''''''''''''''''''''''''''''''

'Written by www.ozgrid.com



'Test to see if a named range exists.

''''''''''''''''''''''''''''''''''''''''

Dim rRange As Range



On Error Resume Next

	Set rRange = Range("MyRange")

		If rRange Is Nothing Then 'Doesn't exist

			MsgBox "The named range does not exist", _

                                vbCritical,"OzGrid.com"

			Set rRange = Nothing

			On Error GoTo 0

		Else 'Does exist

			MsgBox "The named range does exist", _

				 vbInformation,"OzGrid.com"

			Set rRange = Nothing

			On Error GoTo 0

		End If

End Sub

---

 

Sub IsCellVisible()

'''''''''''''''''''''''''''''''

'Written by www.Ozgrid.com



'Test to see if a cell is filtered by autofilters.

''''''''''''''''''''''''''''''''

Dim bHidden as Boolean



   With Sheet1	

    	If .FilterMode = True Then

            bHidden = .Range("A5").EntireRow.Hidden

            MsgBox "Filters are on and A5 row hidden is " & bHidden

    	End If

   End With

End Sub

---

 

Sub HowManyPagesBreaks()

'''''''''''''''''''''''''''''''

'Written by www.Ozgrid.com



'Count how many pages will be printed.

''''''''''''''''''''''''''''''''

Dim iHpBreaks As Integer, iVBreaks As Integer

Dim iTotPages As Integer



    iHpBreaks = Sheet1.HPageBreaks.Count + 1

    iVBreaks =  sheet1.VPageBreaks.Count + 1



    iTotPages = iHpBreaks * iVBreaks

    MsgBox iTotPages

End Sub

 

See also:

Parse/Extract File Name from Full File Name and Path

Excel: Get Maximum Number Between 2 Numbers

Group Excel Worksheets/Sheets by Color

Hide Pivot Table Fields Pivot Items by Criteria

Excel: Get Underlying Hyperlink Address

Excel VBA: Create a List of Hyperlinks

Excel VBA: Gather User Data/Input via an InputBox

Inputbox in Excel VBA

 

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

---

• 
• 
• 
• 
• 
•