EXCEL MAKRO ILE BAĞLANTILI(Linked) WORD TABLOLARININ OLUŞTURULMASI

1 - Bağlantılı(Linked) Word Tablosu Nedir?

    Excel workbook içerisinde hazırlamış olduğumuz tabloların otomatik olarak word belgeleri içerisine nasıl aktarıldığını bir önceki derste öğrenmiştik. 

    Eğer o dersi incelemediyseniz, ilk önce o derse ait sayfayı incelemenizi tavsiye ederim. Çünkü bu ders bir nevi Ders 28’in devamıdır. İncelemek isteyenler için linki hemen altta yer almaktadır.

    Bağlantılı(Linked) Word Tablolarının Oluşturulması dersinde üzerinde duracağımız konular genel itibariyle

 -ilk olarak excel’e bağlı word tablolarının oluşturulması ve 

-excel ile word belgesi arasındaki bağlantının kendini yenileyerek kopmamasını sağlamaktır. 

    Şimdi, excel’de yer alan bir tablonun bağlantılı(linked) olarak word’e aktarılmasını sağlayan kodu inceleyerek derse başlayalım. 

2 - Bağlantılı(Linked) Word Tablosu Oluşturma?

    Örnek olarak 5 adet worksheet’ten oluşan bir workbook hazırlanmıştır. Bu worksheet’lerde ise farazi olarak hazırlanmış olan, otomobil markalarına ait 2018 yılına ait satış rakamlarını ve toplam 2018 yılı satışını gösteren tablolar yer almaktadır. 

    Worksheet’lerin isimleri sırayla; 

    Sheet1(1Ceyrek),

    Sheet2(2Ceyrek),

    Sheet3(3Ceyrek),

    Sheet4(4Ceyrek),

    Sheet5(2018Toplam)’tir.

    İlk örneğimizde sadece ilk sayfa word’e aktarılacaktır. Hepsinin birden aktarılıp çoklu bağlantıların da güncellenmesine dair örnek alt bölümlerde verilmiştir.

    Bu kısımda bir adet tablomuz bağlantılı yani linked olarak word’e aktarılacaktır. İlk olarak örneğimizde kullanacağımız tablomuzu görelim.

 

 

Bağlantılı(Linked) Word Tablolarının Oluşturulması-1Ceyrek

           Bu ilk örneğimizde farklı markaların, modellerine ait 2018 yılı ilk çeyrek satışlarını gösteren tabloyu word’e aktaracağız.(Rakamlar  rastgeledir.)

    Şimdi excelde yer alan bir adet tabloyu bağlantılı olarak word’e aktaran kodu inceleyelim.

EXCEL MAKRO ILE BAĞLANTILI(Linked) WORD TABLOLARININ OLUŞTURULMASI-Code1

 

Not_1:

    Kodu yazmadan önce Tools-References-Microsoft Word X.X Object Library’i Aktifleştirmeyi Unutmayın. Detaylı Bilgi İçin Aşağıdaki Tuş Yardımıyla Ders 28’i Ziyaret Edebilirsiniz.

Kodun Açıklaması

    Ders 28’de Word uygulaması ve Object Library’nin detaylı bir anlatımını yaptığım için burada aynı noktaları üstünkörü olarak geçeceğim.

    1- WordUyg adında, word uygulaması tipinde yeni bir değişken tanımlıyoruz. Bu değişken sayesinde word uygulamasının içinde yer alan bütün fonksiyon, metot vb nesnelere atıfta bulunup, kullanabileceğiz.

    2-İlk madde çalıştırıldığı andan itibaren arka planda word çalışmaya başlar ancak siz çalıştığını sadece “Windows Task Mahager” penceresinde görebilirsiniz. Bu kod ile word ekranda ön plana gelir ve görünür vaziyet alır. Uygulama açıktır ancak, herhangi bir belge henüz yoktur. 

    3- Bu kod ile beraber word uygulamasına yeni bir word belgesi eklenir. F8 ile adım adım kodu çalıştırdığınızda bu aşamaları tek tek görebilirsiniz.

    4/5- Bu satırlarda ise word dosyasını farklı kaydederken isimlendirme aşamasında işimizi kolaylaştırmak adına ilk önce ‘BelgeAdi’ adında bir değişken tanımlayıp, sonrasında ise açık olan workbook’taki 1.sıradaki worksheet’in adını atıyoruz. (index=1). Daha önceleri ThisWorkbook ve Worksheets kavramlarını detaylı olarak işlediğimiz için, burada detaylı olarak anlatmıyorum.

    6- Word belgesine aktarılacak olan tablonun seçimini bu kod ile yapıyoruz. 1Ceyrek isimli sayfada a1 hücresinden başlamış tabloyu, .End() metodu ile ilk önce a sütunun en alt hücresine ordan da en sağdaki hücresine giderek tablo alanını belirliyor ve belirlenen bölgeyi kopyalıyoruz. Burada tablo alanını statik değil de dinamik olarak seçiyor olmamız, tablomuza eklenebilecek satır ve sütunların her seferinde otomatik olarak tablo içerisine alınmasını sağlayacaktır.

    7- Excel’den kopyaladığımız tablonun, word’e aktarılırken nasıl aktarılmasını istediğimizi bu yöntem ile belirliyoruz. Burada kopyalanan tablonun excel tablosu olarak word’e yapıştırılmasını istiyoruz. Bununla beraber 3 adet de parametremiz var. Bizim için önemli olan ilk parametre olan “Linked to Excel As Boolean” parametresidir. Bu parametrede word’e aktarılan tablonun excel workbook’a bağlanmasını sağlıyoruz. Yani ilk parametre ‘TRUE’.  Diğer iki parametreleri False bırakıyoruz. Yine detaylı bilgi isteyenler, DERS 28‘i ziyaret edebilir.

    8- Artık excel’den tablomuzu word’e bağlantılı bir şekilde-linked aktardık. Bu satırda ise kaydediyoruz. Kaydederken, Microsoft’un farklı versiyonlara göre bu kaydetme işlemi  farklılıklar gösterrebiliyordu.  DERS 28‘de bütün bunları detaylı olarak işlemiştik. O yüzden bu yazılan kod Microsoft 2010’a göre yazılmış ve burada paylaşılmıştır. Farklı versiyonlar için  DERS 28 gözden geçirilerek, sizin kullandığınız versiyona göre çok basit bir şekilde uyarlanabilir. 

     SaveAs2’nin bizi bu aşamda ilgilendirdiği tek bir parametresi var o da “BÜTÜN DOSYA YOLU + DOSYA ADI+.doc/docx” . Ben, DosyaAdi isminde bir değişkene worksheet’in ismini atayarak ve Now fonksiyonu kullanarak arkasına günün tarihini ekleyerek her sefereinde farklı bir dosya ismi türetmeye karar verdim. Bu sayede aynı dosyayı farklı isim vermeme gerek kalmadan çalışma esnasında kaydedebiliyorum. Siz isterseniz basitçe ;

    WordUyg.ActiveDocument.SaveAs2 “C:\Users\edagdelen\Desktop\deneme.docx” 

gibi masaüzerine de kaydedebilirsiniz.

    9- Bu son satır ile de kaydedilen dökümanı ve word uygulamasını tamamiyle kapatıyoruz. 

Eğer bu kayıt işlemi gerçekleşirken, işlemi hızlandırmak amacıyla word uygulamasını görmek istemezseniz, basitçe 2 numaralı maddeyi silebilirsiniz.

    Artık excel tablonuzda gerçekleştireceğiniz bütün değişiklikler, word tablosunda da eş zamanlı olarak değişecektir. 

3 - Word Tablolarının Bağlantılarının Manuel Kontorlü

    Bağlantılı(Linked) Word Tablolarının Oluşturulmasında kullandığımız kodu 2 numaralı başlıkta inceledik. Şimdi de word uygulamasını manuel açarak kurmuş olduğumuz bağlantıyı word uygulamasının info ekranından kontrol edelim. 

     Makro ile oluşturduğumuz word belgesini açtığımızda aşağıdaki uyarı penceresi bizi karşılar. Burada kısaca bize bu belgenin bağlantılı olabileceği ve bağlı olduğu dosya bağlantısını güncellemek ister misiniz diye soruyor. Burada ‘YES/EVET’ tıklıyoruz.

    Sonrasında üst sol köşeden ‘File’ sekmesine tıklıyoruz ve aşağıdaki ekranın görünmesini sağlıyoruz.

    File sekmesine tıkladıktan sonra otomatik olarak Info ekranı görünecektir. Info ekranının sağ alt kısmında ise ‘Related Documents’ adında bir kısım göreceksiniz. Farklı versiyonlarda görüntü farkları olsa da genel itibariyle bütün versiyonlarda yerleri aynıdır. Related Document altında “Edit Links to Files”  tıklarsanız da, kurmuş olduğunuz bağlantının detayını görürsünüz.

Links penceresinde özellikle “Source information for seleceted link” başlığı altında bağlantılı oluşturduğunuz tablonuza ait tablo detayı, sheet adı gibi bütün bilgileri görebilirsiniz. 

    Biz 1 adet tablo eklediğimiz için burada sadece tek bir kaynak görebiliyoruz. Sağda tarafta yer alan ‘Update Now’, ‘Open Source’, ‘Change source’ ve ‘Break Link’  seçenekleri ile bağlantınızla alakalı revizeler gerçekleştirebilirsiniz. Özellikle change source seçeneği ile eğer word belgenizin bağlı olduğu kaynak isim veya yer değiştirdiyse, el ile müdehale ederek, bağlantıyı güncelleyebilirsiniz. 

     Ancak eğer bu işlemleri elle yapmak istemiyorsanız, ve bunun için bir makro görevlendirmek isterseniz, bir sonraki bölümde, makro ile otomatik bağlantı güncellenmesinin nasıl yapıldığını öğrenebilirsiniz.

4 - Word Tablo Bağlantılarının Makro İle Güncellenmesi

     Bağlantılı olarak oluşturulan tablolarla alakalı yaşanan sıkıntılardan en önemlisi; kaynak dosyanın isminin değişitirilmesi veya workbook’un başka bir klasöre taşınması sonucunda, word belgesi ile excel arasındaki bağın kopmasıdır. Ne yazık ki word, kaynak ile yaşanan bağlantı hatalarını göstermez. Bir üst bölümde olduğu gibi kaynak dosyayı hiç değişmemiş şekilde göstermeye devam eder. Bir uyarı vermez. Bu sebepten ötürü kaynak olarak kullanılan excel dosyaları üzerinde gerçekleştirilecek olan herhangi bir değişiklik(dosyanın yerinin değişmesi, dosyanın isminin değişmesi v.b.) aynı anda o workbook’u kaynak olarak kullanan word belgelerinin kaynak dosyalarında da değiştirilmelidir. Aksi durumda word belgesi bir sorun göstermeyeceği için, excel ile word arasında hep bir farklılık olacaktır.

    Bu bölümde, kaynak dosyanızın tam dosya uzantısında gerçekleştirebileceğiniz bir revizenin, aynı anda word belgenizdeki kaynak kısmına işlenmesini sağlayan ve word-excel arasındaki bağın devamını sağlayan kodu inceleyeceğiz. 

    

   

Not_2:

    Excel workbook ve word arasında bağlantı gerçekleştirilirken sadece excel-word arasında bir bağ kurulmaz. Kaç adet tablo varsa, bu tabloların her biri için birer adet bağlantı oluşturulur. Yani bir word belgesi bir excel workbook’una bağlandığı zaman, göreceğiniz bağlantı sayısı, kaç adet dosyanın bağlandığına göre değil, aktarılmış olan tablo adedidir.

    1- Word uygulamasının kütüphane, fonksiyon, metot vb nesneleri ile çalışabilmek ve word uygulaması üzerinde işlem yapabilmek için, ilk olarak bir değişken tanımlıyoruz.

    2- Word uygulamasının yanısıra bir adet de word belgesi tipinde değişken tanımlıyoruz. Bu sayede belge üzerinde kontrol sahibi olabileceğiz.

    3-Excel’de yer alan tablolar nasıl birer alansa, bu alanlarında word uygulamasındaki versiyonu field’dir. Yani word tabloları üzerinde bir işlem gerçekleştireceksek, bu tablolara alan olarak tanımlarız ve index(sıra numaraları ile) hangi tabloda işlem yapacağımızı belirtiriz. Bizim bu örnekte sadece bir adet tablomuz olduğu için otomatik olarak Field(1) bizim tablomuzu tanımlayacaktır.

    4- İki numaralı maddede tanımlamış olduğumuz WordBelgesi(Word Belgesi Tipinde) değişkenimize, bağlantısı revize edilecek word belgemizi değer olarak atıyoruz. Bu işlemi WordUyg.Documents.Open ile gerçekleştiriyoruz. Sizde bu işlemi gerçekleştirirken, kendi dosya yolunuzu tam olarak yazmayı unutmayın.

    5- Bir alan olarak tanımlamış olduğumuz wdAlan isimli değişkenimize, artık WordBelgesi’ndeki alanı, yani excel’den kopyalamış olduğumuz tabloyu atıyoruz. 

    6- Bu kod satırı ile word alanın bağlantısı güncellenmiş oluyor. Bağlantı revize edilirken, excel workbook’unun tam dosya yolu ve adı tekrar word alanının özellikleri içerisine aktarılmış oluyor.

    7- Word belgesinde gerçekleştirilen değişiklikler kaydediliyor.

    8- Word uygulaması kapatılıyor. 

     Artık excel dosyanızın yerini veya ismini değiştirdiğinizde bu makroyu çalıştırarak bağlantınızı revize edebilirsiniz.

 

5 - Makro İle Birden Çok Bağlantılı Tablo Oluşturulması

    Genellikle yazdığımız raporlarda birden fazla tablo bulunur. Üstte yer alan bölümlerde bağlantılı tablo aktarılmasının temelini gördük. Bu bölümden sonra ise gerçek hayatımızda daha fazla kullanacağımız çok sayıda bağlantılı tablo aktarmayı göreceğiz. Bu işlemlerde döngülerden faydalanacağız. Döngüler ile yapılan işlemlerde 3 veya 300 tekrar aynı şekilde gerçekleştirileceği için, 5 adet tablonun aktarılmasının yeterli bir örnek oluşturduğuna karar verdik.

     Workbook’umuzun 5 farklı sayfası için 5 farklı tablo hazırladık. Hepsi “A1” hücresinden başlıyor ve dinamik olarak seçilip, kopyalanıp, word’e aktarılıyor. 

     İlk sayfamızda örnekte görmüş olduğunuz ilk çeyrek sonuçları vardı. Diğer sayfalarda ise sırayla 2, 3, 4 Ceyrek ve son olarak da toplam sayıları yansıtan tablolarımız var.

    Şimdi ilk olarak bu tabloları word belgesine aktarmamıza olanak veren Sub-Routine’i inceleyelim.

5.1 - Çok Sayıda Bağlantılı Tablo Aktarımı İçin Hazırlanan Sub-Routine

 

    1- İlk örnekte olduğu gibi word ile yapacağımız işlemlerde ilk işimiz “Microsoft Word Object Library’i çalıştırmak ve sonra da Word’e ilişkin işlemler yapabilmek için word tipinde bir değişken oluşturmak.

    2- Word uygulamasında boş bir sayfa açıyoruz.(Bu örnekte word uygulamasını görmemeyi tercih ettik ve haliyle “WordUyg.Visible=True” kodunu eklemedik. )

    3- 3, 4 ve 5 numaralı maddelerde For Next döngüsünde kullanmak üzere değişkenler tanımladık ve k değişkenine de workbook içerisindeki worksheet adedini değer olarak atadık.

    6- For Next döngüsünü oluşturduk.

    7- Sheets(i) yazarak bütün sayfaların sırayla seçilmesini sağladık ve “A1” hücresimden başlayan tabloları dinamik olarak kopyaladık.(ClipBoard’a)

    8- Clipboard’a kopyalanmış olan tabloları word içerisine her seferde bir adet olacak şekilde kopyaladık ve bir alt satıra geçtik.

    9-Son olarak yine açık olan word belgesini kaydediyoruz ve uygulamayı kapatıyoruz. 

 

 

5.2 - Oluşturulan Linked Word Belgesi

    Oluşan word belgesi;

5.3 - Word Belgesindeki Bağlantılar (Links)

 

Not_3:

    Burada For Next döngüsünü kullanabiliyor olmamıza olanak veren durum, tabloların hepsinin “A” hücresinden başlaması ve A sütunu doğrultusunda ve tabloların son satırları boyunca boş hücre olmamasıdır. 

    Eğer tablolarınız farklı noktalarda ise değişik For Next gibi döngüleri kullanmanız uygun olmayabilir veya biraz daha zor olup, ek birkaç satır daha koda gerek duyabilirsiniz. 

    Yine örnek olarak size For Each ile oluşturduğum Sub-Routine’i de paylaşıyorum.

6 - Makro İle Bağlantılı Tabloların Bağlantılarının Güncellenmesi

    1-Word.Application tipinde, word uygulamasına ulaşabilmemizi sağlayan değikeni tanımlıyoruz.

    2- Bir adet word belgesi tipinde değişken tanımlıyoruz. 

    3- Döngü içerisinde kullanabilmek amacıyla bir adet tam sayı cinsinde değişken tanımlıyoruz.

    4- Word belgesi tipinde tanımladığımız değişkene, bağlantılarının güncellenmesi istenen word belgesini değer olarak atıyoruz.

    5- For Next döngüsü oluşturuyoruz. Döngüde kullanacağımız i’nin üst sınırını ise word belgesi içerisinde yer alan alanların sayısı olarak belirtiyoruz.(WordBelgesi.Fields.Count ile bütün alanları sayabiliyoruz)

    6- Her bir alanın(word’de field, excel’de range) bağlantısını, bu satır vasıtasıyla sırayla excel’in tam dosya adı olarak güncelliyoruz. 

    7 -WordBelgesini kaydedip, uygulamayı kapatıyoruz.

Artık excel dosyasının adının veya yerinin değiştirilmesinden sonra bu makroyu çalıştırarak, word içerisindeki bağlantı kısmına yeni excel dosya uzantınızı otomatik olarak güncelleyebilirsiniz. Burada önemli olan excel’in dosya yolunun değiştirlmiş olmasıdır.

Not_4:

    Burada önemli olan excel’in dosya yolunda meydana gelebilecek değişikliklerin ortaya çıkmasıdır. Workbook isminin değiştirilmesi, farklı bir klasör içerisine taşınması durumunda bu makro, word ile tekrar bağlantı kurulmasını sağlayacaktır. Ancak eğer word belgesinin dosya uzantısını değişirse o zaman, kod içerisinde yer alan

SetWordBelgesi=WordUyg.Documents.Open (“F:\macronimbus\Yazılar\24_UpdatingLinkedWordTables\Coklu_Tablo.docx”)

kodundaki tırnak işareti ile sınırlandırılmış tam dosya yolunun değiştirilmesi gereklidir. Aksi halde makro, farklı bir dosya arayacak, bulamayacak ve Run-Time Error verecektir.

7 - Event Procedures Kullanarak, Linklerin Kendi Kendilerine Güncellenmesinin Sağlanması

    Herhangi bir şekilde excel dosyamızın yerini değiştirmiş ve bunu makroyu çalıştırmayıp, word’deki bağlantıları güncellemeyi unutmuş olabiliriz. Gündelik telaşlar içerisinde bu her zaman başımıza gelebilir. İşte bu nedenden dolayı size tavsiyem “EVENT PROCEDURE” kullanmanız. Event Procedures ile ilgili hatırlatmaya ihtiyaç duyanlar aşağıdaki tuşla ders sayfasına göz atabilir.

    Bildiğin gibi event procedure’de bazı olayların makrolarımızı tetiklemesini sağlayabiliyorduk. Burada yapacağımız şey de aynı. Event Procedure sayesinde kaydetmeden önceki dosya tam adıyla, kaydettiğimiz andaki dosya tam adı karşılaştırılacak, eğer bir farklılık varsa word dosyasındaki bağlantıları güncellemesi için yazdığımız makro tetiklenecek ve bizim çalıştırmamıza gerek duymadan bütün bağlatıları güncelleyecek. Şimdi ThisWorkBook’ a ait event procedure sayfasını ve yazdığımız kodu görelim.

1- İlk kısımda IlkDosyaUzantısı adında, String tipinde dosya yolunun içierisine atanacağı bir değişken tanımlıyoruz.

2-BeforeSave Event’i ile dosyanın kaydedilmeden hemen önce değişkenimize dosyamızın ilk açıldığı andaki tam dosya yolunu kaydediyoruz.

3- Bu kısında ise AfterSave Event’i ile ilk dosya adı ile kaydedilmenin istendiği andaki dosyanın, tam dosya uzantısını karşılaştırıyoruz ve eğer iki dosya yolu birbirinden farklıysa otomatik olarak, bağlantıları güncelleyen ‘Sub-Routine’in çalışmasını sağlıyoruz.

 

 

    Bu sayede bütün bağlantıların güncellenmesi işlemini tam otomatik hale getirmiş oluyoruz. 

 

    Dünya Çapında Araştırmacılara Ait Çalışmaları Okumak İsterseniz

 https://www.researchgate.net/profile/Ender_Dagdelen 

adresini Ziyaret Edebilirsiniz. Ana Sayfaya Dönmek İsterseniz De Aşağıdaki Tuşu Tıklayınız.

Soru ve Düşünceleriniz İçin;

macro[email protected]

Herkese Kolay Gelsin