با تشکر از دوست عزیز جناب آقای شمس العلماء
توانایی اکسل بسیار بالاست و خیلی از کارها رو می تونه انجام بده! در این هیچ شکی نیست! اما وظیفه خودم دونستم که راه های بهتر رو هم برای دوستان مشخص کنم! همچنان معتقدم اکسس برای این کار بهتره! و انجام صحیح این پروژه با اکسل بسیار دشوار تر است! با این وجود به توصیه شما گوش کردم و یک نمونه از این پروژه که با اکسل ایجاد شده برایتان میگذارم!
[size=medium]
اصل فایل اکسل کامل:
http://www.mediafire.com/file/xqgcmvn28e4ah2k/Repair-Cm.xlsm[/size]
برای مقایسه: اگر قرار بود این فایل رو با اکسس درست کنم ده برابر در زمان صرفه جویی می شد. و همچنین نیاز به هیچ کد نویسی نبود زیرا ویزارد های اکسس بسیار راحت هستند.
توضیحات:
ابتدا پیکربندی به صورت زیر است:
به ترتیب جداول به صورت زیر می باشد.
1- جدول قطعات:
2- جدول ماشین ها:
3- جدول تعمیرات:
4- جدول قطعات استفاده شده در تعمیرات:
به این صورت اطلاعات ما در این جداول شکسته می وشد که بسیار کارامد تر از حالت پیوسته است. (بنا به ساختار های رابطه ای بانک های اطلاعاتی)
می توان با کد نویسی بسیار مختصر و استفاده از فرمان های SQL تمامی گزارش های مورد نیاز را درخواست داد.
در روش استفاده از فرمان های SQL ابتدا بایستی به فایل اکسل خودمان به عنوان بانک اطلاعاتی وصل شد.
سپس درخواست SQL را برای این بانک ارسال کرد.
و سپس نتیجه را چاپ کرد.
کد زیر برای وصل شدن به فایل اکسل کنونی است. (activeWorkBOOK)
[size=medium]البته قبل از نوشتن این کد برای فعال سازی ADODB که کلاس خاصی برای وصل شدن به بانک های اطلاعاتی می باشد باید آن را به فایل خود Add کنید. برای این کار به روی Tools و در آن References را انتخاب کنید. در لیست کتابخانه Mirosoft ActiveX Data Objects 2.8 Library را انتخاب کرده و تیک آن را بزنید. و Ok را بفشارید.[/size]
کد:
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Provider = "MSDASQL"
cn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
"DBQ=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "; ReadOnly=True;"
در کد بالا cn شی مورد نظر ماست که ایجاد کردیم. در انتها connectionstring برای وصل شدن به فایل اکسل کنونی را وارد کردیم! دقت فرمایید که از دو مشخصه ThisWorkbook.Path و ThisWorkbook.Name برای ایجاد آدرس فایل کنونی استفاده شده است.
برای درخواست دادن و انتخاب باید از فرمان SQL استفاده کرد که به زودی در بخش بانک های اطلاعاتی به طور کامل توضیح خواهم داد.
به عنوان مثال برای درخواست و چاپ تمامی خرابی ها در ماه 05 می توان از فرمان زیر استفاده کرد.
کد:
strQuery = "Select * From [Sheet1$L:N] where CMDate Like '89/05/%'"
Set RsReport = cn.Execute(strQuery)
Sheet2.Range("A2:Z10000").CopyFromRecordset RsReport
در کد بالا StrQuery متن درخواست است. ترجمه متن درخواست به این صورت است: "همه (*) اطلاعات از جدول [Sheet1$L:N] (که همان ستون های L,M,N از شیت 1است) را انتخاب کن(Select) به شرطی که (where) تاریخ آنها مشابه '05/89' باشد.
"
یا اگر بخواهیم بدانیم هر کدام از ماشین ها چه روز هایی خراب شده اند:
کد:
Dim StrMachineName As String
StrMachineName = 'نام ماشین مورد نظر مثلا سنگ'
strQuery = "Select [Sheet1$M:N].CMDate From [Sheet1$A:B],[Sheet1$M:N] where [Sheet1$A:B].MachineCode=[Sheet1$M:N].MachineCode And [Sheet1$A:B].MachineName= '" + StrMachineName + "'"
Set RsReport = cn.Execute(strQuery)
Sheet2.Range("A2:Z10000").CopyFromRecordset RsReport
همچنین به عنوان مثال می خواهیم بدانیم از هر کدام از قطعات چقدر مصرف شده است. از این کد استفاده می کنیم:
کد:
strQuery = "Select First([Sheet1$G:H].PartName),Sum([Sheet1$P:R].ComsumeNum) From [Sheet1$P:R],[Sheet1$L:N],[Sheet1$G:H] where [Sheet1$P:R].CMCode=[Sheet1$L:N].CMCode And [Sheet1$P:R].CMPart=[Sheet1$G:H].PartCode Group By [Sheet1$G:H].PartCode"
Set RsReport = cn.Execute(strQuery)
Sheet2.Range("A2:Z10000").CopyFromRecordset RsReport
گزارش دیگری که می توان ایجاد کرد گزارش قطعات مصرفی یک ماشین است:
کد:
strQuery = "Select First(PartName), Sum(ComsumeNum) From [Sheet1$P:R],[Sheet1$L:N],[Sheet1$G:H],[Sheet1$A:B] where [Sheet1$A:B].MachineCode=[Sheet1$L:N].MachineCode And [Sheet1$P:R].CMCode=[Sheet1$L:N].CMCode And [Sheet1$P:R].CMPart=[Sheet1$G:H].PartCode and [Sheet1$A:B].MachineName = '" + machine + "' Group BY PartName"
Set RsReport = cn.Execute(strQuery)
Sheet2.Range("A2:Z10000").CopyFromRecordset RsReport
و هر گزارش دیگری که دوست داشته باشید را می توانید با 4 خط برنامه ایجاد کنید.
جستجو، مرتب سازی و ... از مزیت های این روش است.
علاقه مندی ها (Bookmarks)