Workbook ve Worksheet Nesneleri-2

VBA|Seviye|Temel

1.  Açık Olan Tüm Excel Dosyalarının ve Sayfalarının İsimlerini Elde Etmek

Açık olan Excel dosyalarının ve onların sayfalarının isimlerini aşağıdaki şekilde elde edebiliriz.

Not: Excel dosyası ifadesi çalışma kitabını temsil etmektedir. Makalelerde yer alan çalışma kitabı ve Excel dosyası ifadeleri birbirinin yerine kullanılmaktadır.

Private Sub CommandButton1_Click()
Dim Wb As Workbook, Ws As Worksheet, WbWsName As String

For Each Wb In Workbooks
    WbWsName = WbWsName & "Dosya Adı: " & vbNewLine & "     " & Wb.Name & vbNewLine & Chr(9) & "Sayfa Adı: " & vbNewLine
    For Each Ws In Wb.Worksheets
        WbWsName = WbWsName & Chr(9) & "     " & Ws.Name & vbNewLine
    Next Ws
    WbWsName = WbWsName & vbNewLine
Next Wb
MsgBox WbWsName, vbOKOnly, "ishakkutlu.com"

End Sub

Sadece (xlsm, xlsx gibi dosya uzantıları olmaksızın) dosya isimleri yukarıdaki kodlar revize edilerek aşağıdaki şekilde elde edilir.

Private Sub CommandButton1_Click()
Dim Wb As Workbook, Ws As Worksheet, WbWsName As String, OnlyFileName As String

For Each Wb In Workbooks
    OnlyFileName = Left(Wb.Name, Len(Wb.Name) - 5)
    WbWsName = WbWsName & "Dosya Adı: " & vbNewLine & "     " & OnlyFileName & vbNewLine & Chr(9) & "Sayfa Adı: " & vbNewLine
    For Each Ws In Wb.Worksheets
        WbWsName = WbWsName & Chr(9) & "     " & Ws.Name & vbNewLine
    Next Ws
    WbWsName = WbWsName & vbNewLine
Next Wb
MsgBox WbWsName, vbOKOnly, "ishakkutlu.com"

End Sub

2.  Bir Klasörün İçindeki Excel Dosyalarının ve Onların Sayfalarının İsimlerini Elde Etmek

Bir klasörde yer alan Excel dosyalarının, dosya ve sayfa isimlerini elde etmek için aşağıdaki gibi bir algoritmadan yararlanılabilir.

Not: Aşağıdaki algoritmalarda PathFinder değişken ismi ile dinamik dosya dizini kullanılmaktadır. Dinamik dosya dizini ile ilgili bilgi edinmek için “Dizinler” isimli makaleyi inceleyebilirsiniz.

Private Sub CommandButton1_Click()
Dim Wb As Workbook, Ws As Worksheet, WbWsName As String, OnlyFileName As String
Dim PathFinder As String, StrUygPath As String, StrOpenFile As String

Application.ScreenUpdating = False

PathFinder = ThisWorkbook.Path 'Kodların bulunduğu dosyanın dizini bize referans olacak
StrUygPath = PathFinder & "\Uygulama Dosyaları\" 'Uygulama Dosyaları ise kodlardan yöneteceğimiz dosyaların bulunduğu klasör.

StrOpenFile = Dir(StrUygPath & "*.xlsx")
Do While StrOpenFile <> ""
    Workbooks.Open (StrUygPath & StrOpenFile)
    Set Wb = Workbooks(StrOpenFile)
    OnlyFileName = Left(Wb.Name, Len(Wb.Name) - 5)
    WbWsName = WbWsName & "Dosya Adı: " & vbNewLine & "     " & OnlyFileName & vbNewLine & Chr(9) & "Sayfa Adı: " & vbNewLine
    For Each Ws In Wb.Worksheets
        WbWsName = WbWsName & Chr(9) & "     " & Ws.Name & vbNewLine
    Next Ws
    WbWsName = WbWsName & vbNewLine
    Set Wb = Nothing
    Workbooks(StrOpenFile).Close SaveChanges:=False
StrOpenFile = Dir()
Loop

Application.ScreenUpdating = True

MsgBox WbWsName, vbOKOnly, "ishakkutlu.com"

End Sub

StrOpenFile = Dir(StrUygPath & “*.xlsx”) komutu, Do While döngüsü ile birlikte StrUygPath dizininde yer alan tüm Excel dosyalarını tarayıp onların isimlerini veren özel bir döngüdür.  StrOpenFile ifadesinin bir komut değil, String bir değişken olduğuna dikkat ediniz. Do While StrOpenFile <> “” komutu ise StrOpenFile değişkeninin aldığı değerin boş olmaması koşulunu temsil eder. Klasörde taranan dosya bittiğinde ise StrOpenFile değeri boş (“”) bir değer alacaktır. Bu durumda da Do While döngüsü sonlanır. StrOpenFile değeri boş değilse, bu değerin-ismin sahibi olan dosya açılır, Wb değişkenine atanır ve takip eden satırlarda ise önceki işlemlerde olduğu gibi sayfa isimleri elde edilir.

Bir klasörde bulunan Excel dosyalarının sadece ismini elde etmek için şu algoritma yeterli olacaktır.

Private Sub CommandButton1_Click()
Dim WbWsName As String, OnlyFileName As String
Dim PathFinder As String, StrUygPath As String, StrOpenFile As String

Application.ScreenUpdating = False

PathFinder = ThisWorkbook.Path 'Kodların bulunduğu dosyanın dizini bize referans olacak
StrUygPath = PathFinder & "\Uygulama Dosyaları\" 'Uygulama Dosyaları ise kodlardan yöneteceğimiz dosyaların bulunduğu klasör.

StrOpenFile = Dir(StrUygPath & "*.xlsx")
Do While StrOpenFile <> ""
    OnlyFileName = Left(StrOpenFile, Len(StrOpenFile) - 5)
    WbWsName = WbWsName & "     " & OnlyFileName & vbNewLine
StrOpenFile = Dir()
Loop

Application.ScreenUpdating = True

MsgBox "Dosya Adı: " & vbNewLine & WbWsName, vbOKOnly, "ishakkutlu.com"

End Sub

Yukarıdaki algoritmada, bir önceki algoritmanın aksine Excel dosyalarının isimlerinin, dosya açılmadan elde edildiğine dikkat ediniz.

Ayrıca Excel dosyaları yerine, Word veya metin dosyalarının isimlerini elde etmek için yukarıdaki algoritmada StrOpenFile = Dir(StrUygPath & “*.xlsx”) komutunda yer alan uzantı ismini değiştirmeniz yeterli olacaktır. Örneğin Word dosyalarının isimlerini elde etmek için StrOpenFile = Dir(StrUygPath & “*.docx”), metin dosyalarının isimlerini elde etmek için StrOpenFile = Dir(StrUygPath & “*.txt”) şeklinde uzantıları değiştirebilirsiniz.

3.  Bir Sayfayı Kopyalamak, Taşımak ve Konumlandırmak

Bir Excel dosyasında bulunan sayfalar aşağıdaki şekilde kopyalanabilir.

Private Sub CommandButton1_Click()
Worksheets(1).Copy Before:=Worksheets(2)
End Sub

Yukarıdaki metot ile ilk sayfa kopyalanır ve ikinci sayfadan önceki sıraya konumlandırılır.

İlk sayfanın kopyalandığı ve ikinci sayfadan sonraki sıraya konumlandırıldığı örnek aşağıdadır.

Private Sub CommandButton1_Click()
'Worksheets(1).Copy Before:=Worksheets(2)
Worksheets(1).Copy After:=Worksheets(2)
End Sub

İlk sayfanın kopyalandığı ve son sayfadan sonraki sıraya konumlandırıldığı örnek aşağıdadır.

Private Sub CommandButton1_Click()
'Worksheets(1).Copy Before:=Worksheets(2)
'Worksheets(1).Copy After:=Worksheets(2)
Worksheets(1).Copy After:=Worksheets(Worksheets.Count)
End Sub

Kopyalanan yeni sayfanın adını aşağıdaki şekilde değiştirebilirsiniz.

Private Sub CommandButton1_Click()
'Worksheets(1).Copy Before:=Worksheets(2)
'Worksheets(1).Copy After:=Worksheets(2)
Worksheets(1).Copy After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "Sonuncu Sayfa"
End Sub

Eğer adlandırmak istediğiniz sayfa isminin daha önce kullanılıp kullanılmadığını kontrol etmek istiyorsanız, aşağıdaki algoritmayı inceleyebilirsiniz.

Private Sub CommandButton1_Click()
Dim i As Integer, ShtName As Boolean

'Worksheets(1).Copy Before:=Worksheets(2)
'Worksheets(1).Copy After:=Worksheets(2)
ShtName = False
For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Sonuncu Sayfa" Then
        ShtName = True
    End If
Next i

If ShtName = True Then
    MsgBox "Sonuncu Sayfa ismi daha önce kullanıldığı için yeni sayfa kopyalanamıyor.", vbOKOnly, "ishakkutlu.com"
Else
    Worksheets(1).Copy After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = "Sonuncu Sayfa"
End If

End Sub

Var olan bir sayfayı taşımak için yukarıdaki metotlarda sadece Copy yerine Move yazmanız yeterli olacaktır.

Private Sub CommandButton1_Click()
Worksheets(1).Move After:=Worksheets(Worksheets.Count)
End Sub

Yukarıdaki prosedürde Sayfa1, (“Sonuncu Sayfa” isimli) son sayfadan sonraki konuma taşınır.

4.  Bir Sayfayı Başka Bir Excel Dosyasına Kopyalamak

Bir sayfanın başka bir çalışma kitabına kopyalanması sırasında hedef dosya açıksa aşağıdaki şekilde yapılır.

Private Sub CommandButton1_Click()
ThisWorkbook.Worksheets(1).Copy After:=Workbooks("Test.xlsx").Worksheets(Workbooks("Test.xlsx").Worksheets.Count)
End Sub

Aşağıdaki ekran görüntüsü Test dosyasına aittir.

Bir sayfayı başka bir dosyanın içine taşımak için yukarıdaki kodda yer alan Copy ifadesi yerine Move yazmanız yeterlidir.

Yukarıdaki örnekte hedef dosyanın, yani Test dosyasının açık olduğu varsayılmıştı. Bir sayfanın başka bir çalışma kitabına kopyalanması sırasında hedef dosya açık değilse, hedef dosya açıldıktan sonra kopyalama/taşıma işlemi yapılabilir. Hedef dosyanın Uygulama Dosyaları içinde yer alan Test isimli Excel dosyası olduğu varsayılmıştır.

Private Sub CommandButton1_Click()
Dim PathFinder As String, StrUygPath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PathFinder = ThisWorkbook.Path 'Kodların bulunduğu dosyanın dizini bize referans olacak
StrUygPath = PathFinder & "\Uygulama Dosyaları\" 'Uygulama Dosyaları ise kodlardan yöneteceğimiz dosyaların bulunduğu klasör.

Workbooks.Open (StrUygPath & "Test.xlsx")
ThisWorkbook.Worksheets(1).Copy After:=Workbooks("Test.xlsx").Worksheets(Workbooks("Test.xlsx").Worksheets.Count)
Workbooks("Test.xlsx").Close SaveChanges:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

5.  Bir Sayfayı Aktif Yapmak, Gizlemek, Göstermek

Bir sayfayı aktive etmek, o sayfayı açmak, yani o sayfaya gitmek anlamına gelir.

Private Sub CommandButton1_Click()
Worksheets(3).Activate
End Sub

Butonun ilk sayfada olduğu varsayımında, butona tıklanması durumunda Üçüncü Sayfa açılır.

Bir sayfayı gizlemek için aşağıdaki metot kullanılabilir.

Private Sub CommandButton1_Click()
'Worksheets(3).Activate
Worksheets(3).Visible = False
End Sub

Gizlenen sayfanın manuel olarak tekrar görünür yapılması aşağıdaki resimlerde gösterilmiştir. İlk resimde ishakkutlu.com (veya herhangi bir sayfa ismi) üzerine gelip sağ tık yapınız ve takip eden adımları izleyiniz.

VBA ile gizlenen bir sayfayı tekrar görünür yapmak için False yerine True yazmak yeterlidir.

Private Sub CommandButton1_Click()
'Worksheets(3).Activate
'Worksheets(3).Visible = False
Worksheets(3).Visible = True
End Sub

6.  Bir Excel Dosyasını Aktif Yapmak, Gizlemek, Göstermek

“Uygulama Dosyaları” klasörü içinde bulunan Test isimli dosyayı açınız. Test isimli dosyayı simge durumuna getirmeden makroların bulunduğu (zaten açık olan) dosyaya geçiniz. Aşağıdaki prosedürü çalıştırdığınızda ekrana Test isimli dosya gelecektir.

Not: Bir çalışma kitabının aktif olmasının ne anlama geldiğine ilişkin “Workbook ve Worksheet Nesneleri-1” isimli makaleyi incelyebilirsiniz.

Private Sub CommandButton1_Click()
Dim PathFinder As String, StrUygPath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PathFinder = ThisWorkbook.Path 'Kodların bulunduğu dosyanın dizini bize referans olacak
StrUygPath = PathFinder & "\Uygulama Dosyaları\" 'Uygulama Dosyaları ise kodlardan yöneteceğimiz dosyaların bulunduğu klasör.

Workbooks("Test.xlsx").Activate

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Bir Excel dosyasını gizlemek için aşağıdaki algoritmalardan yararlanılabilir.

Private Sub CommandButton1_Click()
Dim PathFinder As String, StrUygPath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PathFinder = ThisWorkbook.Path 'Kodların bulunduğu dosyanın dizini bize referans olacak
StrUygPath = PathFinder & "\Uygulama Dosyaları\" 'Uygulama Dosyaları ise kodlardan yöneteceğimiz dosyaların bulunduğu klasör.

'Workbooks("Test.xlsx").Activate
Windows("Test.xlsx").Visible = False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Gizlenen bir Excel dosyasını tekrar görünür yapmak için aşağıdaki algoritmalardan yararlanılabilir.

Private Sub CommandButton1_Click()
Dim PathFinder As String, StrUygPath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PathFinder = ThisWorkbook.Path 'Kodların bulunduğu dosyanın dizini bize referans olacak
StrUygPath = PathFinder & "\Uygulama Dosyaları\" 'Uygulama Dosyaları ise kodlardan yöneteceğimiz dosyaların bulunduğu klasör.

'Workbooks("Test.xlsx").Activate
'Windows("Test.xlsx").Visible = False
Windows("Test.xlsx").Visible = True

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

7.  Bir Çalışma Kitabının Read Only/Salt Okunur Modda Olduğunu Anlamak

Bir Excel dosyasının read onyl/salt okunur modda olduğunu aşağıdaki şekilde anlayabiliriz.

Private Sub CommandButton1_Click()
Dim Wb As Workbook

Set Wb = ThisWorkbook

If Wb.ReadOnly = True Then
    MsgBox "Dosya read only/salt okunur modda olduğundan kapatılacaktır.", vbOKOnly + vbExclamation, "ishakkutlu.com"
    ThisWorkbook.Close SaveChanges:=False
    GoTo Son
End If
'
'
Son:
'
End Sub

Salt okunur/read only modda açılan bir çalışma kitabında yapılan işlemler kaydedilemez. Bu sebeple çalışma kitabının açılmasını engellemek istersek yukarıdaki algoritmayı BuÇalışmaKitabı (ThisWorkbook) içinde yer alan Workbook, Open tetikleyicisi içine entegre edebiliriz.

Böylece dosya read only ise (örneğin e-postadan doğrudan açmaya çalışıyorsanız) açmaya çalıştığınız sırada otomatik olarak kapanacaktır.

8.  Bir Çalışma Kitabının Açık veya Kapalı Olduğunu Anlamak

Bir Excel dosyasının açık veya kapalı olduğunu bilmek isteyebiliriz. Söz gelimi kodların bulunduğu Excel dosyası dışında başka bir Excel dosyasına (hedef dosyaya) veri yazmak veya ondan veri okumak için hedef dosyayı açmanız gerekir. Halbuki dosya açıksa, VBA komutları ile dosyayı (tekrar) açmaya çalıştığınızda hata oluşabilir. Bu sebeple hedef çalışma kitabının açık olup olmadığını bilmek isteyebilirsiniz.

Öncelikle Project Explorer penceresinde boş bir alana sağ tıklayın ve aşağıdaki resimde gösterilen adımları izleyip standart bir modül oluşturun.

Aşağıdaki algoritmayı modülün içine yapıştırın.

Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long

On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0:    IsWorkBookOpen = False
Case 70:   IsWorkBookOpen = True
Case Else: Error ErrNo
End Select

End Function

Ekran görüntüsü aşağıdadır.

Fonksiyonun adı “IsWorkBookOpen” olarak verildi. Ancak bu ismi dilerseniz “AcikDosyaKontrolu” gibi dilediğiniz bir isimle değiştirebilirsiniz. Ancak fonksiyonun içinde geçen “IsWorkBookOpen” ifadelerinin tümünü “AcikDosyaKontrolu” ifadesi ile değiştirmelisiniz. Anlatıma “IsWorkBookOpen” ismi ile devam edilecektir.

CommandButton1_Click prosederüne ise aşağıdaki kodları yapıştırınız.

Private Sub CommandButton1_Click()
Dim PathFinder As String, StrUygPath As String, StrFileName As String
Dim OpenControl As Boolean

'Dim OpenControl As String 'OpenControl değişkenini Boolean yerine String olarak tanımlasanız da sorun olmayacaktır.
'Ancak OpenControl değişkenini hem Boolean hem de String olarak deklare edemezsiniz.

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PathFinder = ThisWorkbook.Path 'Kodların bulunduğu dosyanın dizini bize referans olacak
StrUygPath = PathFinder & "\Uygulama Dosyaları\" 'Uygulama Dosyaları ise kodlardan yöneteceğimiz dosyaların bulunduğu klasör.
StrFileName = "Test.xlsx"

'Workbooks.Open (StrUygPath & StrFileName)

OpenControl = IsWorkBookOpen(StrUygPath & StrFileName)
If OpenControl = True Then
    MsgBox "Dosya açık.", vbOKOnly + vbInformation, "ishakkutlu.com"
ElseIf OpenControl = False Then
    MsgBox "Dosya kapalı.", vbOKOnly + vbInformation, "ishakkutlu.com"
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Yukarıda, modüle entegre ettiğimiz fonksiyonu, ona verilen isim olan “IsWorkBookOpen” ifadesi ile çağırdık. Fonksiyonun kontrol edeceği çalışma kitabının ise “StrUygPath & StrFileName” dizininde yer alan “Test.xlsx” isimli çalışma kitabı olduğunu belirttik. Module1’de yer alan fonksiyonu inceleyecek olursanız, IsWorkBookOpen fonksiyonunun (Case 0 çalışma kitabının kapalı olduğu durumu ve Case 70 ise çalışma kitabının açık olduğu durumu temsil etmekte olup) False veya True gibi bir değer alacağını görürsünüz. Dolayısıyla IsWorkBookOpen fonksiyonu, “StrUygPath & StrFileName” dizininde yer alan “Test.xlsx” isimli çalışma kitabı için çalıştırıldığında True veya False gibi bir değer alacaktır. Söz konusu bu değer ise OpenControl değişkenine aktarılır. OpenControl değişkeni True ise ilgili çalışma kitabı açık, False ise kapalı demektir.

Alternatif bir uygulama olması bakımından (yukarıdaki prosedürde kullanılan fonksiyonun revize edilerek geliştirilmiş formu olan) aşağıdaki fonksiyonu Module1’e yapıştırın.

Function IsFileOpen(FileName As String)
Dim ff As Long, ErrNo As Long

On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0:    IsFileOpen = False
Case 70:   IsFileOpen = True
Case 53:   GoTo Son
Case Else: Error ErrNo
End Select

Son:
End Function

Bir önceki fonksiyonda küçük bir revizyon yapılarak fonksiyonun kararlılığı arttırıldı ve Excel dosyası dışında, örneğin Word dosyalarının da açık/kapalı olduğunu anlamamızı sağlayacak şekilde genişletildi. Yukarıdaki fonksiyonun kullanımı bir önceki fonksiyonun kullanımı ile aynıdır. Örnek olarak aşağıdaki prosedürü inceleyebilirsiniz.

Private Sub CommandButton1_Click()
Dim PathFinder As String, StrUygPath As String, StrFileName As String
Dim OpenControl As Boolean

'Dim OpenControl As String 'OpenControl değişkenini Boolean yerine String olarak tanımlasanız da sorun olmayacaktır.
'Ancak OpenControl değişkenini hem Boolean hem de String olarak deklare edemezsiniz.

Application.ScreenUpdating = False
Application.DisplayAlerts = False

PathFinder = ThisWorkbook.Path 'Kodların bulunduğu dosyanın dizini bize referans olacak
StrUygPath = PathFinder & "\Uygulama Dosyaları\" 'Uygulama Dosyaları ise kodlardan yöneteceğimiz dosyaların bulunduğu klasör.
StrFileName = "Test.xlsx"

‘Workbooks.Open (StrUygPath & StrFileName)

OpenControl = IsFileOpen(StrUygPath & StrFileName) ‘Tek değişiklik bu satırda
If OpenControl = True Then
    MsgBox "Dosya açık.", vbOKOnly + vbInformation, "ishakkutlu.com"
ElseIf OpenControl = False Then
    MsgBox "Dosya kapalı.", vbOKOnly + vbInformation, "ishakkutlu.com"
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

İshak Kutlu, VBA Notları