Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

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....
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: Excel Duplication Manager Add-in | Excel Number Manager Add-in | Excel Text Manager Add-in | Excel Named Range Add-in Manager | Excel OzGrid Plus Add-in | Excel Time Sheet | Excel Time Wage and Pay book

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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates