vba Bekerja dengan Workbooks

April 12, 2009 at 5:44 am 3 comments

Berikut ini beberapa procedure vba yang digunakan untuk mengecek….

(Kode-kode berikut diperoleh dari OZGRID)

  • Apakah Workbook terbuka atau tidak.
  • Apakah Workbook ada dalam  file dan folder .
  • membuka Semua Workbooks folder tertentu .
  • Apakah  Worksheet tertentu ada dalam active Workbook .
  • Apakah  nama range tertentu ada dalam 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

Entry filed under: Ms Excel, Uncategorized, VBA, Vba Excel. Tags: , .

Data Validasi Dasar Cari Nilai Tertinggi dan Namanya

3 Comments Add your own

  • 1. Edi Susanto  |  April 14, 2009 at 9:20 am

    Wah luar biasa artikel ini… langsung praktekin..

    Reply
  • 2. How I Lost Thirty Pounds in Thirty Days  |  May 4, 2009 at 5:08 am

    Hi, interesting post. I have been thinking about this topic,so thanks for sharing. I’ll certainly be coming back to your blog.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 14 other followers

Blog Stats

  • 94,725 hits

Twitter Updates

Iklan


%d bloggers like this: