Çözüldü Kapalı Sayfalardan Belirtilen Koşulla Göre Veri Aktarmak.

[Haluk]

@gicimi;

Kodların çalışması benim gariban laptop'ta 32 Bit Excel 2010 ile 4,124 saniye sürüyor, sizin Excel 2016 ile 0,120 saniye (12 milisaniye)..... süper doğrusu. Benimkinden 35 kat hızlı.

Herhalde sizin Excel 2016 64 Bit ve bilgisayarınız da bayağı kuvvetli

Kodun sizde Excel 2010 altında çalışmaması ise muhtemelen Access Database Engine ile ilgilidir. Neyse ki, 2016 ile çalıştırabiliyorsunuz.

.

 

[gicimi]

@Haluk Bey;
Bilgisayar özellikleri;

Intel Core i7-7700HQ 2.80GHz
32GB 512GB SSD + 1TB
Nvidia GeForce 4GB GTX 1050/

* Windows 10 TR
* Microsoft Office 2016 Eng. 32 Bit.

 

[Haluk]

@gicimi

Bilgisayarınızın işletim sistemi, donanımı ve Ofis versiyonunuz benim bilgisayara göre bayağı iyiymiş.

Bu arada; yukarıda 70 No'lu mesajıma bir alternatif dosya (Start_HD.xlsm) daha ekledim. O mesajın Not:3 bölümünde de yazdığım gibi; kodların yazıldığı Report.xlsm dosyasında, makronun çalışması sonucunda oluşabilecek hatalardan dolayı Report.xlsm dosyasının bozulması ve verilere daha sonra ulaşılamaması riskine karşı önlem almak üzere, sorgulamadan sonra sonuçların ayrı bir dosyada listelenmesinin bilgi güvenliği için gerekli olduğunu düşündüm. Zira; siz 100.000 - 150.000 adet veriyle çalışmaktan bahsediyorsunuz ki, o zaman fazla veri olan dosyalara kod yazıp çalıştırmak riskli olacaktır. Muhtemelen @Zeki Gürsoy da böyle bir riski öngördüğü için, o da sonuçları ayrı bir dosyada listeletmişti.

Bu alternatif dosyayı, yine diğer Report.xlsx, Data.xlsx, ADM.xlsx ve SDM.xlsx dosyalarının olduğu klasöre yerleştirip, söz konusu 4 adet dosya kapalıyken çalıştırıyorsunuz.

Benim bilgisayarda yaptığım denemelerde, sizin verdiğiniz örnek dosyalardaki verilerle kodun çalışma süresi yaklaşık 6 saniye sürüyor. Deneme yapmak için dosyalardaki verileri 150.000 adete kadar çıkardığımda, çalışma süresi yaklaşık olarak 25 saniye civarında.

Benim nacizane tavsiyem; fazla sayıda veriyle uğraştığınız için kodların harici bir dosyaya yazılarak çalıştırılması güvenli olacaktır.

İyi Pazarlar,

.

 

[gicimi]

@Haluk Bey;

Hata alıyorum ama "Debug" veri geliyor. Hatayı atlatabilir misiniz.

 

[Haluk]

Hata mesajı Sayfa1 'i bulamadığını söylüyor.

ADM.xlsx ve SDM.xlsx dosyalarındaki sayfa adlarını kontrol edin.... "Sayfa1" olması gerekir.

.

 

[gicimi]

Start_HD dosyasının içerisindeki Sayfa isminin Report olmasından olabilir mi.

 

[Haluk]

Start_HD dosyasıyla bir ilgisi yok .... siz diğer dosyaları kontrol edin.

Kodlar, sizin örnek dosyalarınızla sorunsuz çalışıyor. Ayrıca; Start_HD dosyasındaki sayfa ismi zaten "Report" değil, "Rapor". Bu sayfa ismini değiştirmeyin ...

Daha önce 49 No'lu mesajınızda da aynı hatayı aldığınız belirtmiş fakat daha sonra 65 No'lu mesajınızda sorunun çözüldüğünden bahsetmiştiniz.

.

 

[gicimi]

Siteye yükledim dosyaların içerisinde deniyorum. Herhangi bir değişiklik yapmıyorum. Sizdeki verinin aynısı...
ADM - SDM - Data sayfaların hepsinin Sayfa adı "Sayfa1"

Haluk Bey sizin hata almadığınız dosyaları paylaşabilir misiniz. Oradan deneyim. Benim bilgisayardan mı kaynaklı acaba.

 

[Haluk]

Örnek klasör ekli linktedir...

https://drive.google.com/open?id=1yKK5JacdAnlBnuf63vBko2z8NILf74ky

.

 

[gicimi]

Teşekkür ederim. Haluk Bey,

 

[Haluk]

Rica ederim, sorun cozuldu mu?
.

 

[gicimi]

Evet. Kapalı dosyalarından birinin formatı değiştirmişim. Diğerleri ile aynı yaptım. Düzeldi.

 

[Korhan Ayhan]

Merhabalar,

Paylaşımlar için çok teşekkür ederim.

Verilen cevapları biraz önce denedim.

Dosyalardaki veri sayısını 200.000 satıra kadar çoğalttım.

"ADM.xlsx" 200.000 satır
"SDM.xlsx" 200.000 satır
"Data.xlsx" 200.000 satır
"Report.xlsx" 100.000 satır

Aldığım sonuçları paylaşmak isterim. (İ7-2670QM 8 GB laptobumda oluşan sonuçlardır)

Haluk beyin önerisi 114,46 saniye
Zeki beyin önerisi 39,77 saniye
Ziynettin beyin önerisi 25,41 saniye

 

[Haluk]

Korhan Bey, öncelikle konuya ilginiz için teşekkürler.

Bu arada, sanıyorum aynı model laptop kullanıyoruz. İşlemci ve RAM bende de aynı ..... (Excel 2010 32 Bit )

Ben de dediğiniz adette verilerle bir kez daha benim ve Zeki Beyin kodlarını denedim.

Zeki Beyin kodu 45 saniyede, benimki de 01:21,12 dakika yani 81,12 saniyede tamamlandı. Benim kod için sizin belirttiğiniz 114,46 saniye acaba 01:14,46 yani 74,46 saniye olmasın sakın ?

Bu arada, Ziynettin Beyin kodunu da denedim. O da 25 saniyede tamamlandı. Ancak, Report.xlsx dosyasındaki Seri No'lar arasında NULL değerler varsa, kod hata verip çalışmayı durduruyor. Bu NULL değerlere karşı da bir önlem alınması gerekiyor.

Selamlar,

.

 

[gicimi]

Korhan Bey,
Dosyalardaki veri sayısını 200.000 satıra kadar çoğalttım.
Sonuç olarak..::
Haluk Bey, 39 sn.
Zeki Bey, 15 sn.
Ziynettin Bey, 24 sn.

 

[Haluk]

@gicimi ;

Sonuç olarak, bu kadar büyük verilerle uğraşacağınıza göre kesinlikle Zeki Beyin dosyası ile çalışmanız gerekir.

Bunun 1. nedeni veri tabanı olarak sorgulamayı Access'ten yapması, ikinci olarak da kodların harici bir dosyaya yazılarak oradan çalıştırılması.

Ayrıca, sizin esas olarak verilerinizi Excel'de değil Acces' de bir MDB dosyasında tutmanız, her bakımdan daha mantıklı olacaktır.

Selamlar,

.

 

[gicimi]

Haluk Bey tamda dediğiniz gibi. #83 mesajınızdaki belirtiğiniz tavsiye üzerine; fazla sayıda veriyle uğraştığınız için kodların harici bir dosyaya yazılarak çalıştırılması güvenli olacaktır. Bilgi güvenliği açısından tam da dediğiniz gibi Zeki Bey'in hazırlamış olduğu kodlarla çalışacağım. Emeği geçen herkese çok teşekkür ederim.

Sayın @Zeki Gürsoy, @Haluk, @Ziynettin, @Ömer BARAN, @turist Beylere çok minnettarım. Emeğinize sağlık...

 

[Ömer BARAN]

Başından sonuna kadar konuyu takip ettim doğrusu ve "usta"lardan harika cevaplar geldi.

Kanaatim o ki konunun; bir açılış sorusu
(karşılıklı yazışmalarda muallakta kalan hususların netleşmesini sağlayan kısımlar dahil edilerek)
ve verilen nihai çözüm önerilerinin yer aldığı cevaplar derlenerek örnek uygulamalar kısmına alınması çok iyi olur.
Arşivlik kodlar verildi çünkü.
.

 

[Ziynettin]

Bu arada, Ziynettin Beyin kodunu da denedim. O da 25 saniyede tamamlandı. Ancak, Report.xlsx dosyasındaki Seri No'lar arasında NULL değerler varsa, kod hata verip çalışmayı durduruyor. Bu NULL değerlere karşı da bir önlem alınması gerekiyor.

Haluk Bey, NULL olması durumunda On Error Resume Next ile çözüm alabildim.

Sub deneme()
Application.ScreenUpdating = False
Z = TimeValue(Now)
yol = "C:\Veri"
dosya1 = "Data.xlsx"
dosya2 = "ADM.xlsx"
dosya3 = "SDM.xlsx"

GetObject (yol & "\" & dosya1) 'Data
Set s1 = Workbooks(dosya1).Sheets("Sayfa1")
a = s1.Range("C2:O" & s1.Cells(Rows.Count, 3).End(3).Row).Value
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(a): d(a(i, 1)) = i: Next i

GetObject (yol & "\" & dosya2) 'AMD
Set s1 = Workbooks(dosya2).Sheets("Sayfa1")
a1 = s1.Range("D2:H" & s1.Cells(Rows.Count, 4).End(3).Row).Value
Set d1 = CreateObject("scripting.dictionary")
For i = 1 To UBound(a1): d1(a1(i, 1)) = a1(i, 5): Next i

GetObject (yol & "\" & dosya3) 'SMD
Set s1 = Workbooks(dosya3).Sheets("Sayfa1")
a2 = s1.Range("D2:H" & s1.Cells(Rows.Count, 4).End(3).Row).Value
Set D2 = CreateObject("scripting.dictionary")
For i = 1 To UBound(a2): D2(a2(i, 1)) = a2(i, 5): Next i


Workbooks(dosya1).Close
Workbooks(dosya2).Close
Workbooks(dosya3).Close


z1 = CDate(TimeValue(Now) - Z)

On Error Resume Next
Set sh = Sheets("Sayfa1")
b = sh.Range("D2:D" & sh.Cells(Rows.Count, 4).End(3).Row).Value
ReDim c(1 To UBound(b), 1 To 8)
For i = 1 To UBound(b)
    For j = 1 To 7
        c(i, j) = a(d(b(i, 1)), j + 6)
    Next j
    c(i, 8) = d1(b(i, 1))
    If c(i, 8) = "" Then
        c(i, 8) = D2(b(i, 1))
    End If
Next i

sh.[E2].Resize(UBound(b), 8) = c
sh.[F2].Resize(UBound(b)).NumberFormat = "dd.mm.yyyy"
sh.[G2].Resize(UBound(b)).NumberFormat = "hh:mm:ss"
Application.ScreenUpdating = True
z2 = CDate(TimeValue(Now) - Z)
z3 = CDate(z2 - z1)
MsgBox "Kapalı dosya açma kapama süresiniz:  " & z1 & vbLf & vbLf & _
       "Analiz ve verileri yazdırma süresiniz:  " & z3 & vbLf & vbLf & _
       " Toplam geçen süreniz: " & z2, vbInformation
End Sub

 

[Haluk]

Ziynettin Bey;

"On Error Resume Next" ile çözülmüştür muhtemelen ama bu ifade kodun devamında ortaya çıkabilecek başka bir hatayı da görmezden geleceği için ben mümkün mertebe kullanmaktan kaçınırım. Muhtemelen kodun bir yerinde ufak bir IF sorgusuyla bu işi halledebilirsiniz diye düşünüyorum.

En kötü ihtimalle, "On Error Resume Next" ile sorunu sağlıklı bir şekilde çözdüğünüze inanıp, artık devam etmeye başladığınız satırda bu kez "On Error GoTo 0" ile hata denetimini tekrar Excel'e bırakmak, kodun karşılaşabileceği diğer hataları yakalayarak size haber vermesinde yararlı olacaktır.

Selamlar,

.