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

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Back to: Excel VBA . 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 in the active Workbook .
A range is hidden by Auto Filters .
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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft