درس – سؤال ب $100 – تشغيل الماكرو في وقت محدد و خدمات ويندوز

[ابو تراب]

السلام عليكم و رحمة الله

 

لا تستغرب من العنوان فبالفعل هذا سؤال وضع بأحد مواقع الاعمال الحرة Freelancers  و هو كيف يمكن تشغيل ماكرو في وقت معين بدون استخدام Application.OnTime او حتى جدولة المهام Task Scheduler و قد وضع صاحب المشروع ميزانية 100 دولار لمن يجيبه.

 

ليس لدي فكرة اذا تم الاجابة عن هذا السؤال ام لا فلمشروع مغلق الان ... الا انها فرصة لتوضيح كيف يمكن عمل ذلك.

 

الفكرة باختصار هي عمل خدمة ويندوز تشغل الماكرو في وقت محدد.

 

قد يسال احدهم سؤال و ما الفائدة من ذلك...هذا الامر متروك لمدى احتياجك لتشغيل الماكرو اوتوماتيكيا في وقت معين بدون تدخل المستخدم. فمثلا قد يكن من المهم ان تتم عملية الترحيل اوتوماتيكيا في نهاية يوم عمل.

 

ملاحظة

=====

استخدمت جهاز لديه ويندوز 8.1 و اوفس 2010 .. الجهاز غير مربوط باي شبكة محلية.

 

خطوات الحل

=======

1 – عمل ملف اكسل (كتابة كود الماكرو)

2- انشاء ملف VBScript لتشغيل الماكرو

3 – كتابة ملف دفعي او باتش  batch لفحص الوقت

4 – انشاء ملف تنصيب خدمة الويندوز   Windows Service

 

اسال الله التوفيق و السداد

 

[ابو تراب]

1 – عمل ملف اكسل (كتابة كود الماكرو)

2- انشاء ملف VBScript لتشغيل الماكرو

3 – كتابة ملف دفعي او باتش  batch لفحص الوقت

4 – انشاء ملف تنصيب خدمة الويندوز   Windows Service

_____________________________________________________________________

 

بالنسبة للخطوة الاولى و هى كتابة كود الماكرو سنفترض التالي:

 

1- لدينا شيت اسمها Data  وهى تحمل مبيعات اليوم

2- شيت اخرى اطلقنا عليها Summary و تحمل اجمالي المبيعات لجميع الايام.

3- ينفذ الماكرو Summary في وقت معين وذلك لتحديث الشيت Summary بإجمالي المبيعات على حسب المعادلة التالي:

 

'/حدث مبيعات السنة
Sheets(SHEET_SUMMARY).Range("A2") = Val(Sheets(SHEET_SUMMARY).Range("A2")) + Val(Sheets(SHEET_DATA).Range("A2"))

4 - يفضل متابعة سجل تنفيد الماكرو لهذا تم عمل شيت ثالثة باسم Log لمتابعة اوقات تنفيد الماكرو.

 

كود الماكرو

=======

Option Explicit

Public Const SHEET_DATA As String = "Data"
Public Const SHEET_SUMMARY As String = "Summary"
Public Const SHEET_LOG As String = "log"


Public Sub Summary()
Dim Row As Long


'/حدث مبيعات السنة
Sheets(SHEET_SUMMARY).Range("A2") = Val(Sheets(SHEET_SUMMARY).Range("A2")) + Val(Sheets(SHEET_DATA).Range("A2"))




'/حدث اوقات تنفيد الماكرو
Sheets(SHEET_LOG).Range("B3") = Sheets("Log").Range("B3") + 1
Sheets(SHEET_LOG).Range("C3") = Date
Sheets(SHEET_LOG).Range("D3") = Time


Row = Sheets(SHEET_LOG).Range("B3") + 5


Sheets(SHEET_LOG).Range("A" & Row) = Row - 5
Sheets(SHEET_LOG).Range("B" & Row) = Date
Sheets(SHEET_LOG).Range("C" & Row) = Time
Sheets(SHEET_LOG).Range("D" & Row) = Environ("UserName")
Sheets(SHEET_LOG).Range("E" & Row) = Application.UserName
    
End Sub

مخرجات الخطوة الاولى

=================

اسم ملف الاكسل : Summary2015.xlsm

اسم الماكرو : Summary

 

تحياتي

 

 

 

 

Summary2015.zip

[ياسر خليل أبو البراء]

جزيت خيراً أخي الحبيب أبو ذهب على هذا الموضوع الشيق الممتع

في انتظار الخطوة الثانية .. جميل عنصر التشويق ..

بارك الله فيك

[ايهاب سعيد]

ممكن شرح الخطوات 2-3-4

مع الشكر

[ابو تراب]

حياء الله اخي الغالي ياسر يشرفني مرورك   و مشكور على كلماتك الطيبة

 

هلا باخي ايهاب...باذن الله سنشرح جميع الخطوات..و انما قسمتها لتسهيل عملية الشرح و اعطاء من يريد المتابعة فرصة لتجريب.

 

ان شاء الله اليوم اشرح الخطوة الثانية 

 

تحياتي

 

[ايهاب سعيد]

الف شكر علي الاهتمالم

[ابو تراب]

1 – عمل ملف اكسل (كتابة كود الماكرو)

2- انشاء ملف VBScript لتشغيل الماكرو

3 – كتابة ملف دفعي او باتش  batch لفحص الوقت

4 – انشاء ملف تنصيب خدمة الويندوز   Windows Service

____________________________________________

 

الخطوة الثانية هى من اهم الخطوات و ذلك لانها المسؤلة عن تشغيل الماكرو.

 

ملاحظة:

---------

الفجول بايسك اسكربت VBScript ليست VBA و ان كانت تتشابة معها في كتابة الكود.

 

ال VBScript بأختصار هى لغة اسكربت تحمل ملفاتها الامتداد vbs و يمكن تشغيلها بالنقر المزدوج.

 

بالنسبة للخطوة الثانية سنفترض التالي:

 

1 - لا يتم تشغيل الماكرو من قبل الاسكربت الا اذا كان ملف الاكسل مغلقا (اي ليس قيد التشغيل)

Call RunMacroOffline(CurrentFolder & ExcelFile)

2- في حالة ان ملف الاكسل قيد التشغيل، يتم وضع الاسكربت في وضع الانتظار و مراقبة الملف الى ان يتم اغلاقه.

While FileExistsOrInUse(CurrentFolder & ExcelFile) <> NO_ERRORS
WScript.Sleep 5000
WEnd

3 - يمكن لاكثر من نسخة من ملف الاسكربت ان تعمل في نفس الوقت.

4- لا يتقيد ملف الاسكربت بوقت معين لتنفيذ الماكرو (الخطوة رقم ثلاثة هى المسؤلة عن تحديد وقت التنفيذ)

 

 

ملاحظة

=====

بالنسبة لوجود اكثر من نسخة من الاسكربت قيد التشغيل سيخلق لنا مشكلة مشابهة لصورة ادناه:

 

 

مصدر الصورة

 

تسمى بحالة الاستعصاء او Deadlock و هى و جود اكثر من نسحة من الاسكربت تحاول في نفس الوقت الوصول لملف الاكسل و كل نسخة تنتظر الاخرى لتحرر الملف.

 

و لهذا فان الكود:

WScript.Sleep 5000

سيساعد على حل هذه المشكلة وذلك بجعل كل نسخة من الاسكربت الانتظار لمدة خمس ثواني قبل محاول الوصول لملف الاكسل في كل مرة.

 

خطوات أنشاء ملف الاسكربت

 

افتح المذكرة notepad  و اكتب الاسكربت و احفظ الملف بامتداد vbs (اسم الملف هنا سكون RunSummaryMacro.vbs )

 

واليكم كود الاسكربت

Option Explicit

'--------------------------------------------------------
Const NO_ERRORS = 0
Const FILE_NOT_FOUND = 53
''هنا نعرف ملف الاكسل و اسم الماكرو
Const ExcelFile="Summary2015.xlsm"
Const ExcelMacro="Summary"


''نعرف متغييرين الاول يشير لبرنامج الاكسل و الاخر لملف الاكسر
Dim ExcelApp
Dim Workbook


'' متغير يحتفض بمسار المجلد الذي يحتوي على ملف الاسكربت و ملف الاكسل
Dim CurrentFolder
'--------------------------------------------------------


'/ بداية البرنامج


''احصل على مسار المجلد الحالي للاسكربت
CurrentFolder = Left(Wscript.ScriptFullName,(Len(Wscript.ScriptFullName))-(Len(Wscript.ScriptName)))


'' اذا كان ملف الاكسل غير موجود الغي تنفيد البرنامج
If FileExistsOrInUse(CurrentFolder & ExcelFile) = FILE_NOT_FOUND Then Call Wscript.Quit(FILE_NOT_FOUND)


''في حالة ان ملف الاكسل قيد التشغيل .. انتظر 5 ثواني و عد افحص حالة الملف الى ان يتم اغلاق الملف
While FileExistsOrInUse(CurrentFolder & ExcelFile) <> NO_ERRORS
WScript.Sleep 5000
WEnd


'' عندما يكن ملف الاكسل مغلقا شغل الماكرو
Call RunMacroOffline(CurrentFolder & ExcelFile)




'--------------------------------------------------------
'/ دالة فحص ملف الاكسل -- فحص وجود الملف  على الجهاز و كذلك فحص اذا كان الملف قيد التشغيل
Function FileExistsOrInUse(FileName)
Const FOR_APPENDING = 8
Const DO_NOT_OVERWRITE=False


Dim FileSystem
Dim FileRef


Dim Result


Set FileSystem = CreateObject("Scripting.FileSystemObject")


On Error Resume Next
Call Err.Clear


Set FileRef = FileSystem.OpenTextFile(FileName, FOR_APPENDING, DO_NOT_OVERWRITE)


if Err.Number = NO_ERRORS then
Result = NO_ERRORS
On Error Goto 0
Call FileRef.Close
Else
Result = Err.Number
End If


FileExistsOrInUse = Result
End Function


'--------------------------------------------------------
'/اجراء تشغيل الماكرو
Sub RunMacroOffline(FileName)
  
  Set ExcelApp = CreateObject("Excel.Application")
  ''افتح ملف الاكسل
  Set Workbook = ExcelApp.Workbooks.Open(FileName) 
  
  ExcelApp.Visible = FALSE
  ExcelApp.DisplayAlerts = FALSE
  '' شغل الماكرو
  Call ExcelApp.Run(ExcelMacro)
  '' احفظ التغييرات
  Call ExcelApp.ActiveWorkbook.Save
  '' اغلق ملف الاكسل
  Call ExcelApp.ActiveWorkbook.Close
  
  ExcelApp.DisplayAlerts = True
  
  Call ExcelApp.Quit 


  Set Workbook = Nothing 
  Set ExcelApp = Nothing


End Sub

مخرجات الخطوة الثانية

=================

اسم ملف الاسكربت : RunSummaryMacro.vbs

 

 

بالتوفيق

 

 

2.zip

[ياسر خليل أبو البراء]

بسم الله ما شاء الله أخي أبو ذهب...

ربنا يبارك فيك .. ممكن إضافة عمودا لآخر قيمة في مبيعات اليوم قبل إضافتها في شيت الـ Summary ..

[محمد القمجي]

اكثر من رائع

جزاك الله الف خير

[ابو تراب]

هلا و غلا باخي ياسر و اخي محمد و شكرا لاهتمامكم.

 

ربنا يبارك فيك .. ممكن إضافة عمودا لآخر قيمة في مبيعات اليوم قبل إضافتها في شيت الـ Summary ..

 

 

اقتراح جميل فعلا  و يمكن تنفيذه...انما اذا اردتم فأني ارغب فقط في تبسيط الماكرو لكي اوجه التركيز على الية تشغيل الماكرو عن طريق خدمات الوينوز بغض النظر عن المهمة التي يؤديها الماكرو.

 

شاكرا لكم اهتمامكم و ان شاء الله اشرح اليوم الخطوة الثالثة و هي تشغيل الماكرو في وقت معين

 

تحياتي

 

[ابو تراب]

1 – عمل ملف اكسل (كتابة كود الماكرو)

2- انشاء ملف VBScript لتشغيل الماكرو

3 – كتابة ملف دفعي او باتش  batch لفحص الوقت

4 – انشاء ملف تنصيب خدمة الويندوز   Windows Service

___________________________________________

 

الخطوة الثالثة هي عبارة عن ملف باتش يتم تشغيله من قبل خدمة الويندوز طوال فترة تشغيل الجهاز.

 

بالنسبة للخطوة الثالتة سنفترض التالي:

 

1- ملف الباتش عليه الانتظار 30 ثانية قبل محاولة فحص وقت نتفيذ الاسكربت.

TIMEOUT /t 30 /nobreak

2- ملف الباتش سيشغل ملف الاسكربت RunSummaryMacro.vbs كل يوم الساعة 8:30 مساء. و ليتحقق ذلك سنختبر الوقت الحالي بين 8:30 و 8:31 مساء. (يمكنك بالطبع تغيير الوقت حسب ما ترغب)

if !CurTime! gtr 20:30 if !CurTime! lss 20:31 (

3 - ملف الباتش سيحتاج الى تمرير اسم ملف الاسكربت اليه. و يمكن معرفة ملف الاسكربت عبر الوسيط 1%.

 

الكود التالي مسؤل عن تنفيذ الاسكربت:

start wscript //B %1

4 - بعد تنفيذ الاسكربت يجب الانتظار 60 ثانية و ذلك لتجنب تنفيذ اكتر من نسخة من الاسكربت و بالتالي تجنب تنفيذ الماكرو اكثر من مرة.

TIMEOUT /t 60 /nobreak

ملاحظة

=====

اذا اردت تجريب الملف فاتبع الخطوات التالية:

 

1- ضع جميع الملفات (ملف الاكسل و ملف الاسكربت و ملف الباتش في مجلد)

2 - افتح ملف الباتش و ضع وقت التنفيذ الاسكربت قريبا من الوقت الحالي...وتأكد من حفظ الملف.

3- اضغط شعار ويندوز + R و بعدها اكتب cmd

4- انتقل للمجلد الذي فيه الملفات و اكتب التالي:

RunSummaryMacro.cmd RunSummaryMacro.vbs

5 - بعد ان يتم تنفيذ الماكرو يمكنك ايقاف الباتش بالضغط على Ctrl + C

6- لا تنسى التشييك على ملف الاكسل.

 

لمزيد من الايضاح انظر الصورة التالية للملفات الموجود في المجلد 3

 

 

كود ملف الباتش:

 

@ECHO OFF
setlocal enabledelayedexpansion


:start
setlocal


SET CurTime=%time: =0%


if !CurTime! gtr 20:30 if !CurTime! lss 20:31 (


start wscript //B %1


TIMEOUT /t 60 /nobreak
)


TIMEOUT /t 30 /nobreak


GOTO :start


endlocal
goto:eof
@ECHO ON

مخرجات الخطوة الثالثة

=================

اسم ملف الباتش : RunSummaryMacro.cmd

 

 

بالتوفيق

3.zip

[ايهاب سعيد]

رائع وعبقري

[ابو تراب]

هلا باخي ايهاب...شكرا على كلماتك المشجعة

 

اليوم ان شاء الله ننهي الخطوة الاخيرة و هى ربط ملف الباتش بخدمة الويندوز

[ايهاب سعيد]

في انتظار ابدعاتك

[ابو تراب]

1 – عمل ملف اكسل (كتابة كود الماكرو)

2- انشاء ملف VBScript لتشغيل الماكرو

3 – كتابة ملف دفعي او باتش  batch لفحص الوقت

4 – انشاء ملف تنصيب خدمة الويندوز   Windows Service

___________________________________________

 

بسم الله الرحمن الرحيم 

على الله نتوكل وبه نستعين

 

ملاحظة

=====

اذا لم تفرأ الخطوات السابقة بعد فأني اشد على يدك ان تقرأها اولا قبل الاستمرار.

 

سنفترض التالي:

 

1 - جميع الملفات في المسار التالي (بالطبع يمكنك وضعهم في اي مجلد من اختيارك)

C:\work\2015

2 - الخدمة ستعمل تحت حساب النظام المحلي Local System Account

3 - الخدمة ستحتاج الى وسيط وهو اسم ملف الباتش RunSummaryMacro.cmd لتشغيله

4 - الخدمة ستعمل اوتوماتيكيا كل مرة يشتغل فيها الجهاز.

5 - اسم الخدمة هو AbuTorab (يمكنك تسميتها ما تشاء) 

 

 

 

انشاء خدمة ويندوز هى من اكثر الخطوات تشويقا و اثارة..و لهذا سنبدأ على عكس ما بدأناه في الخطوات السابقة....سنبدأ من النهايــــــــــــة.

 

1 - انقر بالزر الايمن و اختر Run as Administrator

 

 

2 - اخل حرف Y للموافقة على تنصيب الخدمة

 

 

3 - تم تنصيب الخدمة بنجاح...لاحظ ان رقم العملية PID للخدمة على جهازي هى 5144

 

 

4 - هنا قد انتهينا من تنصب الخدمة الا انه استكمالا للشرح لنرى الخدمة وهى تعمل في الخلفية.

 

اضغط شعار ويندوز + R  واكتب Services.msc 

 

من هنا يمكنك ايقاف و تشغيل الخدمة

 

 

5 - لنرى الخدمة من شريط المهام

 

اضغط Ctrl + Shift + Esc .. اختر التبويب Services ومن ثم التبويب Details ماذا ترى!!!

 

 

6 - الاسم في التبويب Services هو كما توقعناه AbuTorab ولكن الاسم في التبويب Details مختلف!!!

 

السبب هو

ملف الباتش لا يستطيع من تلقاء نفسه التخاطب مع مركز خدمات الويندوز و لهذا نحتاج الى تغليفه wrap باداة صغير لديها القدرة على التخاطب مع النظام و في نفس الوقت القدرة على تشغيل ملف الباتش و الاداة هى Srvany.exe.

 

توجد ادوات اخرى ولكن استخدمت هذه و يمكنك استخدام ما تريد

 

يمكنك تحميل مجموعة الادوات من موقع شركة ميكروسوفت او يمكنك تحميلها من المرفق

 

ستطلب هذه الاداة ان تعمل لها ثلاث قيم في سجل النظام ... 

 

وهذه القيم هى:

 

AppDirectory : مسار المجلد الذي يحتوي على ملف الباتش

Application : ملف الباتش مع المسار

AppParameters: وسائط ملف الباتش..في مثالنا سيكن ملف الاسكربت

 

ملاحظة:

=====

 

عند انشاء الخدمة سيتم اضافتها الى سجل النظام في المسار التالي:

 

 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\AbuTorab

اذا اردت ان ترها بنفسك فأضغط شعار ويندوز + R و اكتب RegEdit

 

للتوضيح راجع الصورة التالية:

 

 

 

لنتحدث قليل على اوامر انشاء الخدمة Windows Service

================================

 

1- لانشاء الخدمة يمكننا عن طريق الامر sc في محرر الاوامر (هذا الامر يحتاج الى صلاحيات مدير النظام لينفذ)

SC create AbuTorab binpath="C:\work\2015\srvany.exe"

2- لجعل الخدمة تعمل اوتوماتيكيا عن تشغيل/إعادة تشغيل النظام ننفذ الامر التالي:

SC config AbuTorab start=auto

3- لتشغيل الخدمة

sc start AbuTorab

4- لحذف الخدمة

SC delete AbuTorab

5 - لتحديث قيم سجل النظام ننفذ الاوامر التالية

 

reg add "HKLM\SYSTEM\CurrentControlSet\Services\AbuTorab\Parameters" /v AppDirectory /t REG_SZ /d "C:\Work\2015"


reg add "HKLM\SYSTEM\CurrentControlSet\Services\AbuTorab\Parameters" /v Application /t REG_SZ /d "C:\Work\2015\RunSummaryMacro.cmd"


reg add "HKLM\SYSTEM\CurrentControlSet\Services\AbuTorab\Parameters" /v AppParameters /t REG_SZ /d "C:\Work\2015\RunSummaryMacro.vbs"

ملاحظة مهمة و اخيرة

 

اذا نفذت الاوامر السابقة فان الخدمة ستعمل و ملف الباتش سيعمل و ملف الاسكربت سينفذ و لكن لن ينفذ الماكرو

 

برامج الاوفس هى برامج تفاعلية interactive تتفاعل مع حساب المستخدم الذي سجل الدخول وهو ليس حساب تشغيل الخدمة  الا وهو System و لهذا فان الماكرو لن ينفذا...لتحقيق هذا التفاعل نحتاج لوجود مجلد باسم Desktop على المسار التالي:

 

في نسخة الويندوز 64 بت:

c:\windows\SysWOW64\config\systemprofile\Desktop

في نسخة الويندوز 32 بت:

c:\windows\System32\config\systemprofile\Desktop

كود ملف تنصيب الخدمة

 

@ECHO OFF
SET ServiceName=AbuTorab
ECHO A new Windows service called "%ServiceName%" will be installed
ECHO.
CHOICE /C YNC /M "Press Y for Yes, N for No or C for Cancel."
IF %ERRORLEVEL% neq 1 (EXIT)


mkdir %windir%\SysWOW64\config\systemprofile\Desktop
mkdir %windir%\System32\config\systemprofile\Desktop
CLS
SET CurrentDir=%~dp0


sc create %ServiceName% binpath="%CurrentDir%srvany.exe"


reg add "HKLM\SYSTEM\CurrentControlSet\Services\%ServiceName%\Parameters" /v AppDirectory /t REG_SZ /d %CurrentDir%
reg add "HKLM\SYSTEM\CurrentControlSet\Services\%ServiceName%\Parameters" /v Application /t REG_SZ /d "%CurrentDir%RunSummaryMacro.cmd"
reg add "HKLM\SYSTEM\CurrentControlSet\Services\%ServiceName%\Parameters" /v AppParameters /t REG_SZ /d "%CurrentDir%RunSummaryMacro.vbs"


sc config %ServiceName% start=auto
sc start %ServiceName%


ECHO.
ECHO =========================================================
ECHO The %ServiceName% has been installed and run successfully
ECHO =========================================================


SET CurrentDir=
SET ServiceName=


pause
@ECHO ON

مخرجات الخطوة الاخيرة

=================

اسم ملف التنصيب : Installِ_AbuTorab_Service.bat

اسم ملف الاداة : srvany.exe

 

جميع الملفات تجدها على المرفق و يمكنك البدء

 

تم بحمد الله الذي بنعمه تتم الصالحات

 

 

 

 

2015.zip

[وليد زقزوق]

ممتاز

[احمد بلحاج]

مشكورييييييييييييييييييييين

[Tamer_Eshra]

السلام عليكم ورحمة الله وبركاته

بخصوص نفس الموضوع بس فى الاكسس

لو عندى ملفين ماكرو انا بتدخل يدويا لتنفيذ واحد منهم اول كل شهر والتانى يوم 15 من كل شهر.

يا ترى انا اقدر اخلى الاكسس ينفذ الكلام ده تلقائيا فى التاريخ المطلوب

مع تحياتى لحضرات السادة الاعضاء