vba Bekerja dengan Workbooks

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

3 thoughts on “vba Bekerja dengan Workbooks

  1. wah hebat.. Gan biasanya ada pnjlsn sprti if sum vlookup fungsinya begini dg penjlsn begini, kok di vba rumusnya laen,y apa bda dg rms yg biasa at di vb ada rms sndiri

Leave a Reply

Your email address will not be published. Required fields are marked *