Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

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

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 Become an ExcelUser Affiliate & Earn Money

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 [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

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