Makro İle Google E-Tablolardan Veri Almak

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Merhaba,
Excel'de henüz formüllerle bir şeyler yapabilen seviyedeyim. Macro konusuna formüllere hakim olduktan sonra geçmeyi planlıyorum. Ancak üzerinde uğraştığım bir çalışmamda sağolsun @Haluk arkadaşımız aşağıya ekleyeceğim dosyayı oluşturdu ve bende kullanıyorum. Kendisine buradan tekrar teşekkür ediyorum.
Daha önceden dosyadaki isimler gerçek isimler olduğu için forumda paylaşamamıştım. Ancak örnek dosyadaki isimler hayali isimlerle değiştirdiğim için buradada bazı şeyleri sorma gereği duydum.
Sistem şu şekilde çalışıyor.
https://drive.google.com/open?id=1VCIfhT7R5sWEIg99-5ap3SeeCV4FAN2UfhtHLhxioow şeklinde bir e-tablomuz var.
Buradaki isimleri sınıflarına ya da isimlerine göre listelere verdikleri cevapları yerleştiriyor.
Sistem çalışıyor herhangi bir sorun yok.
Haluk bey'e ozaman sınıf sınıf nasıl alırız demiştim oda sınıf sınıf alacak şekilde yaptı ancak projemde değişiklik yapmak zorunda kaldım şimdi sınıf sınıf şeklinde değilde tüm sınıflar tek bir liste şeklinde tek listeye ihtiyaç var. (Bakınız liste isimli sayfaya...)
Aslında bu kodlar liste sayfasına da bu verileri yerleştiriyor.
Buradan şu anlaşılıyor Excel dosyada ne kadar bu isimler varsa sanırım oranın karşısına bu verileri yazıyor. Bu ise benim bu dosyada kullanacağım diğer liste ve sayfalarıda etkiliyor.
Bu kodları nasıl bir değişiklikle excel dosyasında sadece liste isimli sayfaya bu verileri yazmasını sağlayabiliriz?
 

Ekli dosyalar

Haluk

Özel Üye
Katılım
7 Temmuz 2004
Mesajlar
8,880
Excel Vers. ve Dili
32 Bit 2010 - İngilizce
........
......
Sistem çalışıyor herhangi bir sorun yok.
Haluk bey'e ozaman sınıf sınıf nasıl alırız demiştim oda sınıf sınıf alacak şekilde yaptı ancak projemde değişiklik yapmak zorunda kaldım şimdi sınıf sınıf şeklinde değilde tüm sınıflar tek bir liste şeklinde tek listeye ihtiyaç var. (Bakınız liste isimli sayfaya...)
.......
...
Bu kodları nasıl bir değişiklikle excel dosyasında sadece liste isimli sayfaya bu verileri yazmasını sağlayabiliriz?

Ekli dosyadaki revizyon işinizi görür umarım;


.
 

Ekli dosyalar

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Teşekkür ederim @Haluk bey yine hızır gibi yetiştiniz.
Liste ve Data sayfalarında gelen verilerde "Boş" yazan hücrelerde boş yazmaması sadece veri girilmemiş halde yapmak için nasıl bir işlem yapmak gerek? Gelen verilerdeki Boş yazılarını kaldırarak almak ekstra formül yazmaktanda kurtaracak

218355

Ekli dosyadaki revizyon işinizi görür umarım;


.
 

Haluk

Özel Üye
Katılım
7 Temmuz 2004
Mesajlar
8,880
Excel Vers. ve Dili
32 Bit 2010 - İngilizce
Rich (BB code):
        '....
        '..
        objRS.Source = strSQL
        objRS.Open
        
        If objRS.RecordCount > 0 Then
            For j = 0 To objRS.Fields.Count - 2
                tempData = objRS.Fields(j + 1).Value
                If tempData = "Boş" Or tempData = "Bos" Then tempData = Empty
                Sheets("Liste").Cells(i, j + 4) = tempData
            Next
        End If
        '.....
        '...
        '..
.
 

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Teşekkürler.
Şimdiden iyi bayramlar


Rich (BB code):
        '....
        '..
        objRS.Source = strSQL
        objRS.Open
     
        If objRS.RecordCount > 0 Then
            For j = 0 To objRS.Fields.Count - 2
                tempData = objRS.Fields(j + 1).Value
                If tempData = "Boş" Or tempData = "Bos" Then tempData = Empty
                Sheets("Liste").Cells(i, j + 4) = tempData
            Next
        End If
        '.....
        '...
        '..
.
 
Son düzenleme:

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Dikkatimi çeken bir şeyi de sorayım.

objStream.Charset = "Windows-1252"


Windows-1252 - Latin 1 karakterler için değil mi bizim Türkçe için Windows-1254 olması gerekmiyor mu?

Bilmukabele ...

.
 

Haluk

Özel Üye
Katılım
7 Temmuz 2004
Mesajlar
8,880
Excel Vers. ve Dili
32 Bit 2010 - İngilizce
Sizin daha önceden gönderdiğiniz, sorunlu dosyalarda bu karakter koduyla sorunu aşmıştık ...

İsterseniz, "1254" deneyin, bakalım olacak mı?

.
 

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
karakter sounu sanki o google e-tabloya özel bir sorundu
ben genelde yeniden tablo oluşturmuyorum var olan tablonun kopyasını alıp onun üzerinde değişiklik yapıp kullanıyorum.
Türkçe karakter sorunu olan tablolardan değilde sorun olmayan tablolardan kopya aldım.
Bu şekilde bir daha TR karakter sorunu yaşamadım.

Ben daha çok web yazılım konusunda tecrübeliyim. Web sayfalarında TR karakter sorunu yaşamamak için latin 1 yani 1252 değilde latin 5 yani 1254 kullanırız. Gerçi son zamanlarda hep UTF-8 formatını kullanıyoruz...
Oradan kalma aşinalıkla kodların bu kısmı dikkatimi çekmişti.
Sanırım excelde de aynı şekildedir.
1254 olarak değiştireyim ancak şuan TR karakter sorunu olmadığı için çokta önemli bir durum değil.

Teşekkürler tekrar

Sizin daha önceden gönderdiğiniz, sorunlu dosyalarda bu karakter koduyla sorunu aşmıştık ...

İsterseniz, "1254" deneyin, bakalım olacak mı?

.
 

Haluk

Özel Üye
Katılım
7 Temmuz 2004
Mesajlar
8,880
Excel Vers. ve Dili
32 Bit 2010 - İngilizce
Kodlardaki "Utf8ToUnicode" fonksiyonu zaten daha önceki "ImportData" prosedüründe kullanılıyordu, şimdiki "ImportData2" prosedüründe zaten kullanılmıyor.

Yani; söz konusu "Utf8ToUnicode" fonksiyonunu kodlardan komple silseniz bile, revize kodlar yine çalışır. Ama eğer ihtiyaç duyarsanız, tıpkı "ImportData" prosedüründe kullanıldığı gibi kullanabilirsiniz.

Ben sizin ne gibi geliştirmeler yapacağınızı bilmediğim için, belki ihtiyacınız olur diye; "ImportData" prosedürünü ve "Utf8ToUnicode" fonksiyonunu revize dosyadaki kodlardan silmemiştim.

Karar sizin ...

.
 

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Teşekkürler
formüllerden makroya geçişimde etken olacak olan kodlar sizin bu kodlarınız olacak inşallah.

Kodlardaki "Utf8ToUnicode" fonksiyonu zaten daha önceki "ImportData" prosedüründe kullanılıyordu, şimdiki "ImportData2" prosedüründe zaten kullanılmıyor.

Yani; söz konusu "Utf8ToUnicode" fonksiyonunu kodlardan komple silseniz bile, revize kodlar yine çalışır. Ama eğer ihtiyaç duyarsanız, tıpkı "ImportData" prosedüründe kullanıldığı gibi kullanabilirsiniz.

Ben sizin ne gibi geliştirmeler yapacağınızı bilmediğim için, belki ihtiyacınız olur diye; "ImportData" prosedürünü ve "Utf8ToUnicode" fonksiyonunu revize dosyadaki kodlardan silmemiştim.

Karar sizin ...

.
 

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Örnek dosyada Liste sayfasındaki Şıkların girildiği sütunlar 10 adetti bunu 20 ya da 30 yaptığımzda Data sayfasına tüm veriler geliyor ancak Liste sayfasına gelmiyor bu ayarlamayı hangi satırdan yapmalıyız?
Sanırım bu işlem Sub ImportData2() içerisinde yapılıyor.


Kod:
Sheets("Liste").Range("D9:M" & NoListe) = ""
D9:M sanırım buradaki M'i değiştirip 20 tane veri için W yapacağız

Kod:
Sub ImportData2()
'   Haluk - 23/05/2020
'   sa4truss@gmail.com
    Dim NoData As Integer, NoListe As Integer, i As Integer, j As Byte
    Dim objADO As ADODB.Connection
    Dim objRS As ADODB.Recordset
    Dim strFile As String
    Dim strSQL As String
   
    NoListe = Sheets("Liste").Range("A" & Rows.Count).End(xlUp).Row
    NoData = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
   
    Sheets("Liste").Range("D9:M" & NoListe) = ""
   
    Set objADO = New ADODB.Connection
    Set objRS = New ADODB.Recordset
   
    strFile = ThisWorkbook.FullName
   
    With objADO
        If Val(Application.Version) < 14 Then
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .Properties("Extended Properties") = "Excel 8.0; HDR=No;IMEX=1;"
        Else
            .Provider = "Microsoft.Ace.OLEDB.12.0"
            .Properties("Extended Properties") = "Excel 12.0; HDR=No;IMEX=1;"
        End If
'       .CursorLocation = adUseClient
       .ConnectionString = strFile
       .Open
    End With
   
    With objRS
       .CursorType = adOpenStatic
       .CursorLocation = adUseClient
       .LockType = adLockBatchOptimistic
       .ActiveConnection = objADO
    End With
   
    For i = 9 To NoListe
        strSQL = "Select * from [Data$E3:O" & NoData & "] where F1 = " & Sheets("Liste").Range("B" & i)
       
        objRS.Source = strSQL
        objRS.Open
       
        If objRS.RecordCount > 0 Then
            For j = 0 To objRS.Fields.Count - 2
                tempData = objRS.Fields(j + 1).Value
                If tempData = "Boş" Or tempData = "Bos" Then tempData = Empty
                Sheets("Liste").Cells(i, j + 4) = tempData
                'Sheets("Liste").Cells(i, j + 4) = objRS.Fields(j + 1).Value
            Next
        End If
       
        If objRS.State = adStateOpen Then objRS.Close
    Next
   
    If objADO.State = adStateOpen Then objADO.Close
   
    Set objRS = Nothing
    Set objADO = Nothing
End Sub
218363
 
Son düzenleme:

Haluk

Özel Üye
Katılım
7 Temmuz 2004
Mesajlar
8,880
Excel Vers. ve Dili
32 Bit 2010 - İngilizce
Revize dosya ektedir, ben deneyemedim.... Siz deneyince haber verirsiniz.



.
 

Ekli dosyalar

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Teşekkürler.
Çalıştı şimdi üstteki ile en son eklenen iki kod arasındaki kodları karşılaştırıyorum.

Son bir durum kaldı
öğrenci numarakarının olduğu B:B sütununun soluna bir sütun daha Eklemek İstiyorum.... (Bu durumda öğrenci numaraları ve isimler bir sütun sağa kaymış oluyor)
Tabiki böyle olunca kodlar çalışmayacak çünkü denemedim. Ancak çalışmadı.
Çalışmaması normal çünkü kodlarda belirli sütun ve satırlara göre hareket ediyor
Bunun için önceki ve sonraki kodları karşılaştırıp çözmeye çalışıyorum.
İnşallah çözebilirim

218374
Revize dosya ektedir, ben deneyemedim.... Siz deneyince haber verirsiniz.



.
 

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Kod:
strSQL = "Select * from [Data$E3:" & strLastCol_Data & NoData & "] where F1 = " & Sheets("Liste").Range("B" & i)
Range("B" & i)
Bu kısmı
Range("C" & i)
yaparak çalıştırdım ancak sonuçlar bir sütun ileri kaymadı :)
 

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Kodları değişkenleri MsgBox ile tektek sayfaya yazdırdım ve işlem sıralarını aldıkları değerleri inceledim.
Yapıyı çözdüm ancak

Kod:
strSQL = "Select * from [Data$E3:O" & NoData & "] where F1 = " & Sheets("Liste").Range("B" & i)
sql sorgusundaki where şartını sorgulayan F1 in ne olduğunu anlayamadım.

İlgili kısımda 9. satırdan itibaren veri olan satır sayısı kadar for/next döngüsü oluşturulmuş.

SQL sorgusu ile verilerin olduğu hücreler içerisinde F1 e eşit olan öğrenci numaraları eşleştirilerek Liste sayfasında j sayacı kadar döngüye alınmış...

Buraları anladım ancak SQL sorgudaki yine öğrenci numarası olan ama F1 olararak adlandırılmasını anlayamadım.

Buraya neden F1 yazıldığını açıklayabilir misinz?
 
Son düzenleme:

Haluk

Özel Üye
Katılım
7 Temmuz 2004
Mesajlar
8,880
Excel Vers. ve Dili
32 Bit 2010 - İngilizce
.......
....
..
Buraya neden F1 yazıldığını açıklayabilir misinz?

ADO bağlantısını kurarken;

Kod:
.Properties("Extended Properties") = "Excel 8.0; HDR=No;IMEX=1;"
veri tablosundaki alanlarla ilgili olarak HDR=No (Header ≡ Başlık) kullanarak, ADO'ya sütun başlıkları "Yok" diye belirttik. O zaman ADO, tablodaki veri alanlarınının başlıklarını otomatik olarak kendisi F1, F2, F3 .... diye adlandırır.


Verilerin alınmasına yönelik olarak hazırladığımız sorguda ise;

Kod:
strSQL = "Select * from [Data$E3:" & strLastCol_Data & NoData & "] where F1 = ........
kullanmıştık. Burada; alacağımız verilerin "Data" sayfasında "E3" hücresinden yani, Öğrenci No'ların olduğu sütundan başlayacağını belirttik.

Sorgulayacağımız veri alanının sol üst köşesi "E3" olduğuna göre; "E" sütunu bizi ilgilendiren veri alanının 1. sütunu demektir. O zaman, "HDR=No" dediğimiz için ADO bu sütunu F1 olarak adlandıracaktır. Veri tablosundaki bu "F1" alanı da, bizim sorgulama kriteri olan Öğrenci No'larını barındıran alandır.

Olay kısaca bundan ibarettir....


.
 
Son düzenleme:

merrrakli

Altın Üye
Katılım
27 Ocak 2010
Mesajlar
204
Excel Vers. ve Dili
Türkçe Microsoft Office Professional Plus 2016
Açıklama için teşekkürler.
Anladığım kadarıyla ilk sütun F oluyor satırda 1 olduğu için F1 mi diyoruz başka alanlar olduğunda olara G1, H1... şeklinde devam mı ediyor yoksa tek F olarak mı kullanılıyor?


üstteki resimdeki gibi B:B sutünün soluna bir sütun eklediğimde aşağıdaki değğişiklikleri yaptım ve çalıştı.
Eksik/yanlış yaptığım bir şey yoktur inşallah.


Kod:
Sheets("Liste").Range("D9:" & strLastCol_Liste & NoListe) = ""
Liste sayfasında önceden var olan verileri temizlemek için D sütunundan başlayan kısmı E sütunundan başlattım.
Kod:
Sheets("Liste").Range("E9:" & strLastCol_Liste & NoListe) = ""

Kod:
strSQL = "Select * from [Data$E3:" & strLastCol_Data & NoData & "] where F1 = " & Sheets("Liste").Range("B" & i)
Liste sayfasında öğrenci numaraları C sütununa kaydığı için sql cümlemizin öğrenci numaralarını aldığı B sütunundan C sütununa aldım.
Kod:
strSQL = "Select * from [Data$E3:" & strLastCol_Data & NoData & "] where F1 = " & Sheets("Liste").Range("C" & i)

Kod:
Sheets("Liste").Cells(i, j + 4) = tempData
verilerin değerini j+4. satırdan başlıyordu bunu j+5. satır olarak düzenledim
Kod:
Sheets("Liste").Cells(i, j + 5) = tempData

Bunların dışında eksik/yanlışım var mı? ya da yapmam gereken başka bir şey..

ADO bağlantısını kurarken;

Kod:
.Properties("Extended Properties") = "Excel 8.0; HDR=No;IMEX=1;"
veri tablosundaki alanlarla ilgili olarak HDR=No (Header ≡ Başlık) kullanarak, ADO'ya sütun başlıkları "Yok" diye belirttik. O zaman ADO, tablodaki veri alanlarınının başlıklarını otomatik olarak kendisi F1, F2, F3 .... diye adlandırır.


Verilerin alınmasına yönelik olarak hazırladığımız sorguda ise;

Kod:
strSQL = "Select * from [Data$E3:" & strLastCol_Data & NoData & "] where F1 = ........
kullanmıştık. Burada; alacağımız verilerin "Data" sayfasında "E3" hücresinden yani, Öğrenci No'ların olduğu sütundan başlayacağını belirttik.

Sorgulayacağımız veri alanının sol üst köşesi "E3" olduğuna göre; "E" sütunu bizi ilgilendiren veri alanının 1. sütunu demektir. O zaman, "HDR=No" dediğimiz için ADO bu sütunu F1 olarak adlandıracaktır. Veri tablosundaki bu "F1" alanı da, bizim sorgulama kriteri olan Öğrenci No'larını barındıran alandır.

Olay kısaca bundan ibarettir....


.
 

Ekli dosyalar

Son düzenleme:

Haluk

Özel Üye
Katılım
7 Temmuz 2004
Mesajlar
8,880
Excel Vers. ve Dili
32 Bit 2010 - İngilizce
Açıklama için teşekkürler.
Anladığım kadarıyla ilk sütun F oluyor satırda 1 olduğu için F1 mi diyoruz başka alanlar olduğunda olara G1, H1... şeklinde devam mı ediyor yoksa tek F olarak mı kullanılıyor?
......
...

Bunu yukarıdaki mesajımda şöyle açıklamıştım;

ADO bağlantısını kurarken;

Kod:
.Properties("Extended Properties") = "Excel 8.0; HDR=No;IMEX=1;"
veri tablosundaki alanlarla ilgili olarak HDR=No (Header ≡ Başlık) kullanarak, ADO'ya sütun başlıkları "Yok" diye belirttik. O zaman ADO, tablodaki veri alanlarınının başlıklarını otomatik olarak kendisi F1, F2, F3 .... diye adlandırır.
.......
....
Buradaki "F1" ifadesi "Field1"in kısaltmasıdır. Yani, birinci alan F1, ikinci alan F2 ......

Yaptığınız düzeltmelere bakmadım çünkü örnek dosya yok, kontrol ettiyseniz sorun olmaz herhalde....

.
 
Üst