Makro İle Google E-Tablolardan Veri Almak

merrrakli · 23 Mayıs 2020

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?

Muhammet Okumuş · 23 Mayıs 2020

Hocam burdaki dosya işinize yarar mı?

Haluk · 23 Mayıs 2020

merrrakli' Alıntı:
........
......
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;

.

merrrakli · 23 Mayıs 2020

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

Haluk' Alıntı:
Ekli dosyadaki revizyon işinizi görür umarım;

.

Haluk · 23 Mayıs 2020

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 · 23 Mayıs 2020

Teşekkürler.
Şimdiden iyi bayramlar

Haluk' Alıntı:

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
        '.....
        '...
        '..

.

Haluk · 23 Mayıs 2020

Bilmukabele ...

.

merrrakli · 23 Mayıs 2020

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?

Haluk' Alıntı:
Bilmukabele ...

.

Haluk · 23 Mayıs 2020

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

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

.

merrrakli · 23 Mayıs 2020

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

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

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

.

Haluk · 23 Mayıs 2020

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 · 23 Mayıs 2020

Teşekkürler
formüllerden makroya geçişimde etken olacak olan kodlar sizin bu kodlarınız olacak inşallah.

Haluk' Alıntı:
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 · 23 Mayıs 2020

Ö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

Haluk · 23 Mayıs 2020

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

.

merrrakli · 24 Mayıs 2020

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

Haluk' Alıntı:
Revize dosya ektedir, ben deneyemedim.... Siz deneyince haber verirsiniz.

.

merrrakli · 24 Mayıs 2020

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 · 24 Mayıs 2020

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?

Haluk · 24 Mayıs 2020

merrrakli' Alıntı:
.......
....
..
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....

.

merrrakli · 24 Mayıs 2020

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..

Haluk' Alıntı:
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....

.

Haluk · 24 Mayıs 2020

merrrakli' Alıntı:
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;

Haluk' Alıntı:
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....

.

Makro İle Google E-Tablolardan Veri Almak

merrrakli

Ekli dosyalar

Muhammet Okumuş

Destek Ekibi

Haluk

Özel Üye

Ekli dosyalar

merrrakli

Haluk

Özel Üye

merrrakli

Haluk

Özel Üye

merrrakli

Haluk

Özel Üye

merrrakli

Haluk

Özel Üye

merrrakli

merrrakli

Haluk

Özel Üye

Ekli dosyalar

merrrakli

merrrakli

merrrakli

Haluk

Özel Üye

merrrakli

Ekli dosyalar

Haluk

Özel Üye