बहु-उपयोगकर्ता मैक्रो अपडेट किया गया

मुझे नहीं पता कि कैसे प्रोग्राम करना है, लेकिन वास्तव में प्यार!
कलाकार Vasya Lozhkin द्वारा थोड़ा संशोधित उद्धरण।
मैं VBA एक्सेल पर एक बहु-उपयोगकर्ता प्रणाली को बढ़ाने के अनुभव के साथ लेख साझा करना चाहता हूं।
एक लचीला अनुप्रयोग बनाने के निर्णय के समय, बड़े खंडों में लगभग 7 मैक्रोज़ काम कर रहे थे (20 हजार लाइनों से 370 हजार लाइनों तक कई फाइलें), जिनका वजन 50 किलोबाइट से 12 मेगाबाइट तक था, जिनमें से प्रत्येक को ज्ञान के अनुसार लिखा गया था लेखन के समय मौजूदा। प्रत्येक मैक्रो को त्रुटियों के संदर्भ में बदला गया, जोड़ा गया, सही किया गया और यह देखते हुए कि 60 से अधिक लोगों ने इन मैक्रो का उपयोग किया, जिनमें से सभी ने परिवर्तनों को ट्रैक नहीं किया, उन्होंने लगातार मुझे एक और त्रुटि दिखाते हुए खींच लिया, जिसे मैंने पहले ही सही कर दिया और सभी को भेज दिया। मैंने भीड़ को यह समझाते हुए छोड़ दिया कि मैक्रोज़ का सही उपयोग कैसे किया जाए, क्योंकि कोई सुन नहीं पाएगा, कोई यह नहीं समझ पाएगा कि वे किस बारे में बात कर रहे हैं, कोई मैक्रोज़ के साथ काम करने के लिए पहले से ही तैयार टेबल ले लेगा, और मैं यह अनुमान नहीं लगा सकता कि कौन और कैसे टेबल बदलेगा।

एक कोड बनाना आवश्यक था, न कि मेल द्वारा भेजे गए प्रत्येक परिवर्तन की 60 प्रतियां।

समाधान जल्दी ही मेरे दिमाग में आ गया, और Google ने जल्दी से मुझे प्रोग्रामेटिक रूप से VBA कोड बदलने के परिणाम दिए, जो बाद में व्यवहार में वापस आ गया। तो, तस्वीरों के बिना पाठ दिलचस्प नहीं है, यहां पहला है, यह एप्लिकेशन की संरचना है, जो इस तरह दिखता है:


फ़ाइल खोलने वाला उपयोगकर्ता, एक्सेल ईवेंट "ओपन बुक" को सक्रिय करके "क्लाइंट" फ़ाइल में स्थित प्रक्रिया करता है। प्रक्रिया नेटवर्क पर फ़ाइलों को पढ़कर एक मेनू बनाती है। वांछित मैक्रो के लिए बटन पर क्लिक करके, क्लाइंट में एक मॉड्यूल बनाने के लिए एक कार्रवाई की जाती है, नेटवर्क पर स्थित एक फ़ाइल से, क्रिया करता है और अपने भीतर की प्रक्रिया को नष्ट कर देता है।

हमें क्या मिलता है:
- किसी भी मैक्रो के लिए फ़ाइल हमेशा एक होती है।
- मैक्रो के निष्पादन पर क्लिक करने पर, उपयोगकर्ता हमेशा नवीनतम कोड का उपयोग करेगा, जो मैक्रो समर्थन को सरल करता है।
- गलती से एक लॉग फ़ाइल लिखने की क्षमता, उपयोगकर्ता को इंगित करने की जरूरत है, जिसने सब कुछ बर्बाद कर दिया है।

इसके अलावा कोड पर बहुत सारे कॉपी-पेस्ट होंगे, मेरे कामों के लिए, मैं लेखकों को ख़ुशी से इंगित करूँगा, लेकिन मुझे ईमानदारी से पता नहीं है, मैंने बुर्जुआ संसाधनों से बहुत कोड लिया, कुछ रूसी से, कुछ मैंने आविष्कार किया, यह सब कम से कम छह महीने तक संचित रहा, इसलिए अग्रिम में स्रोत से पहले एक बड़ा खेद है, अगर कुछ, लिखना, हटाना।

तो, व्यावहारिक हिस्सा है।

हम पुस्तक के उद्घाटन के लिए एक घटना लटकाते हैं:

Private Sub Workbook_Open() create_module_for_file End Sub 

कोड मुश्किल नहीं है, अवधारणा एक है, आगे की कार्रवाई के लिए एक किक दें
Create_module_for_file प्रक्रिया:

 filemod = OpenFileModule("menu") cl_d.create_module ("menu") cl_d.write_sub_for_module_action filemod, "menu" filemod = OpenFileModule("list_action") cl_d.create_module ("list_action") cl_d.write_sub_for_module_action filemod, "list_action" 

तीन लाइनें तीन क्रियाएं, शुरुआत में हमें मॉड्यूल के साथ एक फ़ाइल लेने की आवश्यकता होती है। इसके लिए निम्न कार्य जिम्मेदार है:

 Function OpenFileModule(namemodule) Set FSO = CreateObject("Scripting.FileSystemObject") filepath = "-//  //-" mSearch = ".txt" OpenFileModule = FSO.GetFile(filepath & "\" & namemodule & mSearch) End Function 

अगला, मॉड्यूल बनाएं:

 Function create_module(name_module) '       ThisWorkbook.VBProject.VBComponents.Add vbext_ct_StdModule '     k = ThisWorkbook.VBProject.VBComponents.Count '     ThisWorkbook.VBProject.VBComponents.Item(k).Name = name_module End Function 

और फ़ाइल से मॉड्यूल तक कोड लिखें

 Function write_sub_for_module_action(filepath, filename) Open filepath For Input As #1 s = "" Do Until EOF(1) Line Input #1, Data s = s & Data & z & z Loop Set vbComp = ThisWorkbook.VBProject.VBComponents(filename) With vbComp.CodeModule .InsertLines .CountOfLines + 1, s End With Set vbComp = Nothing Close #1 End Function 

फ़ाइलें प्राप्त करने के बाद, हम मेनू को लोड करते हैं।
लोड करते समय, एक मेनू बनता है जिसे ऐड-ऑन में रखा जाता है

कोड द्वारा:
-एक शुरुआत के लिए हम हटा देंगे, अगर कोई अपडेट होगा:

 Application.CommandBars(1).Controls("MTS_K").Delete -   <source lang=«VBScript»> '   , ,        MenuPos = Application.CommandBars(1).FindControl(ID:=30010).Index + 1 '   Set Menu = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, before:=MenuPos, temporary:=True) '     Menu.Caption = "MTS_K" 

पैनल बनाया गया था, अब हम सीधे एक मेनू बनाते हैं जो मैक्रोज़ के साथ प्रत्येक फ़ाइल के लिए सीधे बटन बनाएगा।

 '     Set Menuname = Menu.Controls.Add(Type:=msoControlPopup) '     Menuname.Caption = " YES->NO" 

मेनू बनाने के बाद, बटन जोड़े जाते हैं। मेनू एक फ़ाइल -> एक बटन के सिद्धांत पर बनाया गया था। बदले में, मैक्रो के साथ एक एकल फ़ाइल में कई प्रक्रियाएं हो सकती हैं जिन्हें कॉल करने की आवश्यकता होती है, यहां उप मेनू में वे पॉप होते हैं। यह कोड 3 बटन बनाता है:

 Set SubItem = MenuItem.Controls.Add(Type:=msoControlButton) '   SubItem.Caption = "  " ' ,      SubItem.FaceId = "801" '    ,     SubItem.OnAction = "action11a" '     SubItem.Enabled = True '    Set SubItem = MenuItem.Controls.Add(Type:=msoControlButton) SubItem.Caption = " " SubItem.FaceId = "1038" SubItem.OnAction = " action12a" SubItem.Enabled = True Set SubItem = MenuItem.Controls.Add(Type:=msoControlButton) SubItem.Caption = "   " SubItem.FaceId = "1038" SubItem.OnAction = " action13a" SubItem.Enabled = True 

परिणाम कुछ इस प्रकार है:

लेखन मोड में, काम की अवधारणा को बदल दिया। यह कार्य कोड के नवीनतम संस्करण का उपयोग करने के लिए किया गया था, जब आपने "मैक्रो निष्पादित करें" पर क्लिक किया था। इन परिस्थितियों को देखते हुए, कुछ फाइलें क्लाइंट को डाउनलोड की जाएंगी, हम उन्हें इंडेक्स फाइल्स कहेंगे, यानी फाइल ओपन होने के बाद जो मॉड्यूल्स लगातार मौजूद होते हैं, वे "मेनू" और "लिस्ट एक्शन" होते हैं, और मैक्रो फाइल्स खुद एक बटन के क्लिक पर लोड हो जाएंगी। उनका क्या अर्थ है: एक मेनू फ़ाइल एक मेनू बनाती है :)

 Set MenuItem = PLEXMenu.Controls.Add(Type:=msoControlPopup) MenuItem.Caption = "  " Set SubItem = MenuItem.Controls.Add(Type:=msoControlButton) SubItem.Caption = "  " SubItem.FaceId = "801" SubItem.OnAction = "action11" SubItem.Enabled = True 

मेनू में हम एक घटना देखते हैं जो "एक्शन 1 ... एक्स" प्रक्रिया को संदर्भित करता है, जो दूसरी फ़ाइल "लिस्ट_एक्शन" में बैठता है। आइए इसकी सामग्री देखें

 Sub action11() Set cl_d = New Edit_module On Error Resume Next cl_d.delete_modul_full ("action_yes_no") filemod = OpenFileModule("action_yes_no") cl_d.create_module ("action_yes_no") cl_d.write_sub_for_module_action filemod, "action_yes_no" kart_view End Sub 

हम क्या करते हैं, सबसे पहले, हमें इसे लोड करने और इसका उपयोग करने के लिए मॉड्यूल को निकालना होगा, चाहे कोड बहुत बदल गया हो। अगला, हम फ़ाइल खोलते हैं, एक मॉड्यूल बनाते हैं और उसमें फ़ाइल से कोड लिखते हैं।
इन जोड़तोड़ के बाद, हमें उस प्रक्रिया को कॉल करना होगा जो जोड़तोड़ करता है, जो प्रक्रिया को निष्पादित करने के लिए बटन पर क्लिक करके निहित थे।
यहाँ दिलचस्प है)
यदि हम उस प्रक्रिया के नाम का संकेत देते हैं जो सीधे ऊपर दिए गए कोड में स्थित है, तो हमारी कार्रवाई 11 प्रक्रिया यह शपथ लेती है कि वह "kart_view" प्रक्रिया नहीं खोज सकती, और सही है, क्योंकि वास्तव में यह उस समय नहीं था जब बटन क्लिक किया गया था, इस मामले में एक जीवन हैक का उपयोग किया गया था। एक फ़ंक्शन बनाया जाता है, जो बदले में इस प्रक्रिया को कॉल करता है, कोड है:

 Sub action11() Set cl_d = New Edit_module On Error Resume Next cl_d.delete_modul_full ("action_yes_no") filemod = OpenFileModule("action_yes_no") cl_d.create_module ("action_yes_no") cl_d.write_sub_for_module_action filemod, "action_yes_no" action11a End Sub Function action11a() kart_view End Function 

यही है, "क्रिया 11a" से "kart_view" कॉल करने के समय, इस प्रक्रिया को पहले से ही मॉड्यूल में लोड किया जाएगा, vba एक पकड़ नहीं देखता है।
परिणामी परिणाम: बटन पर क्लिक करें, कोड को मॉड्यूल में लोड किया गया है, निष्पादित किया गया है। बार-बार निष्पादन के मामले में, मॉड्यूल को हटा दिया जाता है, फिर से लोड किया जाता है, निष्पादित किया जाता है। जब आप फ़ाइल को बंद करते हैं, तो पुस्तक को जगह नहीं लेने और अगले खोलने तक स्थिर रहने के लिए सब कुछ साफ हो जाता है।
बड़ी फ़ाइलों को संसाधित करने पर कुछ और शब्द, मुझे लगता है कि यह दिलचस्प होगा। जैसा कि उल्लेख किया गया है, फ़ाइलों का उपयोग बड़ी मात्रा में किया जाता है, जिनमें से 300 हजार से अधिक लाइनें हैं, जिनमें से आपको एक नियम के रूप में, 10–30 हजार चुनना चाहिए और उनके साथ काम करना चाहिए।
अगर मुझसे पूछा जाता कि आधे साल पहले से एक नमूना कैसे बनाया जाता है, तो मैं इसे बिना जाने शर्त के साथ चक्र जानूंगा। बाद में, कई तरीकों की कोशिश की गई थी, मैं हर एक का वर्णन करूंगा, इसलिए, जानकारी के लिए, उन लोगों को छोड़कर, जिन्हें मानक एक्सेल फ़ंक्शन के साथ लागू किया जा सकता है।
प्रारंभिक डेटा: 300 हजार पंक्तियों की एक तालिका है, 10 हजार मान ज्ञात हैं जिनकी हमें आवश्यकता है।
1) सबसे पहले मैंने चयन पाश बनाने की कोशिश की

 tab1 =   tab2 =   col_tab1 = tab1.Cells(Rows.Count, 1).End(xlUp).Row '    (10 ) col_tab2 = tab2.Cells(Rows.Count, 1).End(xlUp).Row '    (300 ) for i = 1 to col_tab1 for ii = 1 to col_tab2 if tab1.cells(ii,1) = tab2.cells(i,1) then  exit for end if next ii next i 

यदि हम चक्र पूरा नहीं करते हैं, तो हमें यह पता चलता है कि परिणाम जानने के लिए हमें 300,000 लाइनों के माध्यम से 10,000 बार छंटनी चाहिए। कुल प्रति सेकंड लगभग 500 पंक्तियाँ होंगी
3000000000/500 = 6000000 सेकंड, (100000 मिनट या 1666.66 घंटे या 69 दिन या 9 सप्ताह या ...)
और परिणाम आम तौर पर यहाँ और अब, अच्छी तरह से, या अधिकतम दो मिनट के बाद की जरूरत है, ताकि यह सही न हो।
2) मेरे द्वारा उपयोग की जाने वाली दूसरी विधि mysql सर्वर के साथ काम कर रही है
Mysql सर्वर के साथ काम करने के लिए, जो पहले से ही एक अन्य परियोजना के लिए था, mysql ODBC ड्राइवर की आवश्यकता थी, मुझे 2, संस्करण 3.51 और संस्करण 5.1 पता था। यह पसंद 3.51 पर गिर गई, दूसरी, मंचों पर अपने काम की चर्चा के अनुसार, बहुत अधिक नहीं है। एन्कोडिंग के साथ अच्छी तरह से काम किया, लेकिन मैं utf8 में आधार चाहता था
मुझे एक ड्राइवर मिला, और मैंने जिस पहले टक्कर पर ठोकर खाई, वह अधिकार है

यही है, आपको बाहर से कनेक्ट करने के लिए एक खाते की आवश्यकता है, हमारे मामले में यह एक vba- मैक्रो होगा जो इस तरह से शपथ लेता है:

स्मोक्ड एसक्यूएल मैनुअल, यह पाया

 CREATE USER '%'@'user' IDENTIFIED BY PASSWORD 'mtspass'; GRANT SELECT ON *.* TO '%'@'user'; 

यही है, ऊपर के अनुरोध के अनुसार, रूट अधिकारों वाले किसी भी उपयोगकर्ता के लिए एक खाता बनाया जाता है।
स्वाभाविक रूप से, किसी ने मुझे रूट अधिकार नहीं दिया और डेटा को स्टोर करने के लिए mysql सर्वर का उपयोग करने वाली परियोजना "जमी" थी क्योंकि भविष्य में डेटाबेस में लिखना आवश्यक था, मैंने केवल पढ़ा था।
प्रभावशीलता का मूल्यांकन करने के लिए: चक्रों का उपयोग करते हुए पुनरावृत्तियों का उपयोग करने वाले एक ही मैक्रो ने 59 सेकंड के लिए काम किया, एसक्यूएल के साथ 3.5-4 सेकंड में ऐसा करना संभव था, अर्थात यह वास्तव में 10 से अधिक बार तेजी लाने में कामयाब रहा।
कोड के अनुसार, एक अच्छा लेख यहाँ है: egregors.blogspot.ru/2013/05/mysql-vba-excel-mysql.html
3) sql थीम को धूम्रपान करना जारी रखते हुए, मैंने पहुंच के साथ काम करना शुरू किया।
कोड नारकीय है, लेकिन काम कर रहा है।

 Set cN = New ADODB.Connection '  cN.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=-//  //-\base\base" & Environ("USERNAME") & ".mdb; Jet OLEDB:Database;" '     '      for i = 1 to col_tab1 nabor_act = nabor_act & “','” & tab1.cells(i,1) next i Set RS = New ADODB.Recordset '       RS.Open "SELECT * FROM kartoteka WHERE nom_act LIKE '” & right(nabor_act, len(nabor_act)-3) & ”' ;", cN, adOpenStatic, adLockOptimistic o = 2 Do While Not RS.EOF ter.Cells(o, 1) = "" & Right$(RS.Fields(1).Value, 6) ter.Cells(o, 2) = RS.Fields(2).Value ter.Cells(o, 3) = "" ter.Cells(o, 4) = RS.Fields(6).Value ter.Cells(o, 5) = "" o = o + 1 RS.MoveNext Loop cN.Close Set cN = Nothing 

यह विधि किसी कारण से mysql की तुलना में अधिक धीमी गति से काम करती है, लेकिन यह उपयोग के लिए काफी स्वीकार्य है। लेकिन मुझे नहीं ...
मैंने इसे पहली बार मना कर दिया था मुझे एक ऐसी प्रणाली की आवश्यकता थी जिसमें एक उपयोगकर्ता एक विशिष्ट फ़ोल्डर में एक डेटाबेस बनाता है और तुलना और गणना के लिए 300 हजार पंक्तियों की 2 तालिकाएं लिखता है। परीक्षणों के दौरान, मुझे लगभग 600 मीटर आकार का आधार मिला, यह देखते हुए कि भविष्य में 50 लोग एक ही समय में इस मैक्रो का उपयोग कर सकते हैं, यह आकार में 25-30 गिग्स के बारे में एक फ़ोल्डर का पता लगाता है, जिसके लिए व्यवस्थापक मुझे तुरंत दूर करेंगे।
4) वास्तव में जिस तरह से मैंने रोका। हां, यह दूसरे और तीसरे तरीकों की तुलना में धीमी गति से काम करता है, लेकिन इन कमियों की भरपाई कार्यक्षमता द्वारा की जाती है। पुस्तक द्वारा Sql क्वेरी

 Set cn = New ADODB.Connection '    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.path & "\" & ActiveWorkbook.Name & ";Extended Properties=Excel 12.0;" '    cn.ConnectionTimeout = 40 cn.Open '      for i = 1 to col_tab1 nabor_act = nabor_act & “','” & tab1.cells(i,1) next i sql = "SELECT * FROM [res$] WHERE [ ] '” & right(nabor_act, len(nabor_act)-3) & ”' " Dim rs As New ADODB.Recordset Set rs = New ADODB.Recordset '    rs.ActiveConnection = cn '     rs.LockType = adLockOptimistic '              .          Update().            . rs.CursorLocation = adUseClient '     rs.source = sql '  rs.Open rs.MoveFirst '     rew = "" Do Until rs.EOF '   rew = rew & "','" & rs.Fields(0).Value rs.MoveNext '     Loop rs.Close cn.Close 

यहां ... यदि हम उदाहरण की शर्तों को लेते हैं, तो एक मौका के लिए 10 सेकंड के आसपास एक लाइन अनुरोध के लिए एकत्र की जाएगी, वांछित परिणाम के आधार पर, 25 सेकंड के लिए क्वेरी नमूना के साथ लटकाएगी, प्रसंस्करण के लिए 10 सेकंड।
कुछ इस तरह।