<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

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

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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 IfEnd 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 WithEnd 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 WithEnd 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 IfEnd Sub

 Sub DoesRangeExist()'''''''''''''''''''''''''''''''''''''''''Written by www.ozgrid.com'Test to see if a named range exists.''''''''''''''''''''''''''''''''''''''''Dim rRange As RangeOn 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 IfEnd 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 WithEnd Sub

 Sub HowManyPagesBreaks()''''''''''''''''''''''''''''''''Written by www.Ozgrid.com'Count how many pages will be printed.''''''''''''''''''''''''''''''''Dim iHpBreaks As Integer, iVBreaks As IntegerDim iTotPages As Integer    iHpBreaks = Sheet1.HPageBreaks.Count + 1    iVBreaks =  sheet1.VPageBreaks.Count + 1    iTotPages = iHpBreaks * iVBreaks    MsgBox iTotPagesEnd 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. ALLpurchases 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

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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