درس ملف اكسل في برنامج VB

درس حول كيفية التعامل مع الاكسل باستخدام الفيجوال بيسك

هذا الموضوع منقول لتعم الفائدة



اولاً يجب إضافة مكتبة اكسل من قائمة Project > References
Microsoft Excel 10.0 Object Library
( العشرة تعني اوفيس اكس بي 2002 )

كود إنشاء ملف اكسل يحتوي على ورقة عمل واحدة وحفظ الملف على C

Public xlApp As Excel.Application
Public xlBook As Excel.Workbook

Sub CreateNewWorkBooks()

Set xlApp = CreateObject("Excel.Application")
xlApp .SheetsInNewWorkbook = 1 ' تكوين ورقة عمل واحدة فقط

Set xlBook = xlApp.Workbooks.Add 'تكوين مصنف جديد
xlApp.Windows(1).ActiveSheet.Name = "sheet" 'اعطى اسم لورقة العمل

xlBook.SaveAs FileName:="C:\file_name.xls" 'حفظ الملف
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

End Sub

---------------------------------------------
حفظ الملف

xlBook.Save ' اذا كتب هذا الكود فقط لحفظ الملف فإنه يحفظ الملف في مجلد الستندات باسم - بوك1

xlBook.SaveAs FileName:="C:\file_name.xls" 'حفظ الملف مع تحديد مكان واسم الملف

xlBook.Close saveChanges:=True 'حفظ التغيرات التي اجرية على الملف
'اذا لم يحفظ الملف في خطوة سابقة ويحدد اسم ومكان الملف في هذه الحالة تظهر نافذة الحفظ

xlBook.Saved = True

------------------------------------------
إظهار المصنف اثناء العمل عليه

xlApp.Visible = True
'في حالة لا تريد اظهار الصنف لا تكتب هذا الكود

-------------------------------------------
Common Dialog فتح ملف باستخدام

Components :
- Microsoft Common Dialog Control 6.0
( CommonDialog1

CommonDialog1.CancelError = True
On Error GoTo ErrHandler

CommonDialog1.FileName = ""
CommonDialog1.Filter = "Excel files: ( *.xls ) |*.xls|"
CommonDialog1.ShowOpen
If CommonDialog1.FileName = "" Then Exit sub
If Dir(CommonDialog1.FileName) = "" Then Exit Sub
Set xlBook= Workbooks.Open(CommonDialog1.FileName)
Application.Visible = True
..................
..................

xlBook.Close
' اغلاق الملف بدون حفظ التعديلات
أو
xlBook.Close saveChanges:=True 'اغلاق الملف مع حفظ التعديلات
Set xlBook = Nothing
ErrHandler:
' User pressed Cancel button.
Exit Sub

-------------------------------------------
Common Dialog حفظ ملف باستخدام

CommonDialog1.CancelError = True
On Error GoTo ErrHandler

CommonDialog1.FileName = "C:\file_name.xls"
CommonDialog1.Filter = "Excel files: ( *.xls ) |*.xls|"
CommonDialog1.ShowSave
If CommonDialog1.FileName = "" Then Exit sub
..................
..................

xlBook.SaveAs FileName:= CommonDialog1.FileName
Set xlBook = Nothing
ErrHandler:
' User pressed Cancel button.
Exit Sub

------------------------------------------
طريقة أخرى لفتح ملف اكسل

CommonDialog1.CancelError = True
On Error GoTo ErrHandler

CommonDialog1.FileName = ""
CommonDialog1.Filter = "Excel files: ( *.xls ) |*.xls|"
CommonDialog1.ShowOpen
If CommonDialog1.FileName = "" Then Exit Sub
If Dir(CommonDialog1.FileName) = "" Then Exit Sub
Excel.Workbooks.Open (CommonDialog1.FileName)
Application.Visible = True
..................
..................

Excel.Workbooks.Close
ErrHandler:
' User pressed Cancel button.
Exit Sub

-------------------------------------------
إضافة ورقة عمل جديدة

Public xlSheet As Excel.Worksheet

Set xlSheet = xlBook.Worksheets.Add 'اضافة ورقة عمل
xlSheet.Name = "sheet_name" 'اعطى اسم لورقة العمل

-------------------------------------------
حذف ورقة العمل

xlApp.DisplayAlerts = False
Worksheets("sheet_name").Delete
xlApp.DisplayAlerts = True
السطر الاول والثاني لمنع ظهور رسالة تأكيد عملية الحذف
--------------------------------------------
الكتابة في خلية

Cells(Row, Column)

xlSheet.Cells(1, 2).Value = "Welcome" 'كتابة كلمة في الصف الاول والعمود الثاني
'طريقة اخرى
xlSheet.Cells("A2").Value = "Welcome"
'طريقة اخرى
xlSheet.Range("A2").Value = "Welcome"
-------------------------------------------

ملاحظة :ـ
يمكن تحديد ورقة العمل التي تعمل عليها
اما باستخدام اسم الذي عرفته لورقة العمل
مثل xlSheet
أو
Worksheets(1) حسب ترتيب الورقة وهي هنا الاولة
أو
Worksheets("Sheet") تحديد الورقة للعمل عليها باسمها اذا كان معروفاً

هذا مثال على ذلك
xlSheet.Range("A1:H11").Font.Size = 12
Worksheets(3).Range("A1:H11").Font.Size = 12
Worksheets("Sheet").Range("A1:H11").Font.Size = 12

-------------------------------------------
تنسيق الخلايا

xlSheet.Range("A2").Font.Size = 12 'حجم الخط
xlSheet.Range("A2").Font.Name = "Times New Roman" ' اسم الخط
xlSheet.Range("A2").HorizontalAlignment = xlCenter 'المحاذاة الافقية
xlSheet.Range("A2").WrapText = True 'التفاف النص داخل الخلية
xlSheet.Range("A2").VerticalAlignment = xlVAlignCenter 'المحاذاة العمودية
xlSheet.Range("A2").Borders.Weight = xlThin 'تكوين حدود الجدول

تغيير لون الخط في كل اوراق العمل اي في المصنف
ActiveWorkbook.Styles("Normal").Font.ColorIndex = 3
طريقة اخرى
ActiveWorkbook.Styles("Normal").Font.Color = RGB(0, 0, 255)

هي احد الانمط Normal
ولعرض كل الانماط الموجودة في الورقة الاولة نفذ هذا الكود
For i = 1 To ActiveWorkbook.Styles.Count
Worksheets(1).Cells(i, 1) = ActiveWorkbook.Styles(i).Name
Next

هذا الكود ينفذ نمط واحد على المصنف وهو نمط خط اسود عريض
ActiveWorkbook.Styles("Normal").Font.Bold = True

إنشاء نمط جديد
With ActiveWorkbook.Styles.Add(Name:="MyStyle")
.Font.Size = 12
.Font.Bold = True
.Font.Name = "Times New Roman"
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With

هذا الكود لستخدام النمط الجديد
xlSheet.Range("B2:B10").Style = "MyStyle"

تنسيق الارقام
Worksheets("sheet_name").Cells(1, 1).NumberFormat = "###,##0.00"

-------------------------------------------
تحديد مجموعة من الخلايا للعمل عليها

Worksheets("sheet_name").Range("A1:H10").Value = "Welcome"

Worksheets("sheet_name").Range(Worksheets("sheet_n ame").Cells(1, 1), Worksheets("sheet_name").Cells(10, ).Value = "Welcome"

بطريقة اخرى
With Worksheets("sheet_name")
.Range(.Cells(1, 1),.Cells(10, ).Value = "Welcome"
End With

بطريقة اخرى
Dim c1, c2 As Variant
Set c1 = Worksheets("sheet_name").Cells(1, 1)
Set c2 = Worksheets("sheet_name").Cells(10,
Worksheets("sheet_name").Range(c1, c2).Value = "Welcome"

ويمكن عمل هذا مع تنسيق الخلايا مثل
Worksheets("sheet_name").Range("A1:H10").Font.Size = 12

xlsheet.Range("A2", "H11").Font.Size = 12

-------------------------------------------
تغيير عرض العمود
xlSheet.Cells(1, 1).ColumnWidth = 15

تغيير ارتفاع الصف
xlSheet.Range("A1").RowHeight = 20

-------------------------------------------
دمج الخلايا
Worksheets("sheet_name").Range("C11:C12").Merge

-------------------------------------------
استخدام الدوال

xlSheet.Range("A1").Value = 1
xlSheet.Range("A2").Value = 2
xlSheet.Range("A3").Value = 3

xlSheet.Range("A4").Formula = "=SUM(A1:A3)"
xlSheet.Range("A5").Formula = "=AVERAGE(A1:A3)"

-------------------------------------------
( / * - + ) العمليات الحسابية

xlSheet.Range("C1").Value = (xlSheet.Range("A1").Value ) * (xlSheet.Range("B1").Value)

-------------------------------------------
حماية ورقة العمل من الكتابة او التعديل

xlSheet.Protect Password:="123", Scenarios:=True

-------------------------------------------
ترتيب ورقة العمل

وضع ورقة العمل الاخيرة في الترتيب
xlSheet.Move after:=Sheets(Sheets.Count)
' تعني اخر ورقة Sheets.Count

نقل اكثر من ورقة عمل ووضعهم قبل الورقة الاولة
Sheets(Array("Sheet2", "Sheet4")).Move before:=Sheets(1)

-------------------------------------------
البحث عن ورقة عمل باسمها داخل المصنف

For i = 1 To Worksheets.Count
If Worksheets(i).Name = "sheet_name" Then
'اذا كانت الورقة موجودة نفذ االاومر التالية تم اخرج من جملة الفور
..............
..............
Exit For
End If
Next i


-------------------------------------------
إضافة اكثر من ورقة عمل مع الترتيب

Worksheets.Add Count:=2, before:=Sheets(1)
' الكود يقوم بإضافة ورقتين عمل ووضعهم في قبل الورقة الاولة

-------------------------------------------
إضافة ورقة عمل من نوع تخطيط بحيث يكون ترتيبها الثانية

Sheets.Add after:=Sheets(2), Type:=xlChart

-------------------------------------------
فرز بيانات عمود معين فرز تصاعدي

xlSheet.Range("a1:a10").Sort Key1:=Range("a1")

-------------------------------------------
نسخ خلايا معين من ورقة العمل بكل قيمه وتسيقاته ولصقه في مكان اخر

Worksheets(2).Range("A1:H12").Copy
Worksheets(1).Range("A1").PasteSpecial (xlPasteAll) 'لصق خاص - لصق كل شئ
Worksheets(1).Range("A1").Select


-------------------------------------------
استخدام الدوال

xlSheet.Range("A4").Formula = "=sum(A1:A3)"

-------------------------------------------

التعبئة التلقائية للخليا

xlSheet.Cells(2, 2).Value = 1
xlSheet.Cells(3, 2).Value = 2
xlSheet.Range("B2:B3").AutoFill Destination:=xlSheet.Range("B4:B10"), Type:=xlLinearTrend

-------------------------------------------
حذف صف او عمود

xlsheet.Range("A1").EntireRow.Delete
xlsheet.Range("A1").EntireColumn.Delete

-------------------------------------------
إضافة صف او محموعة صفوف
xlsheet.Range("A2").EntireRow.Insert
xlsheet.Range("A2:A5").EntireRow.Insert
إضافة عمود
xlsheet.Range("B2").EntireColumn.Insert

-------------------------------------------
احفاء وإظهار الاعمدة
بتحديد رقم الصف او العمود

xlsheet.Columns(2).Hidden = True
xlsheet.Rows(3).Hidden = True

---------------------------------------------------
بعض الخصائص لتحكم في طريقة عرض مصنف الاكسل

xlApp.Windows(1).DisplayRightToLeft = True 'تغيير اتجاه ورقة العمل من اليمين الى اليسار
xlApp.Windows(1).DisplayGridlines = False 'إظهار وإخفاء خطوط شبكة الجدول
xlApp.Windows(1).DisplayFormulas = False 'إظهار وإخفاء شريط الصيغة
xlApp.Windows(1).DisplayHeadings = False 'إظهار وإخفاء رؤوس الاعمدة والصفوف
xlApp.Windows(1).DisplayHorizontalScrollBar = False 'إظهار وإخفاء شريط التمرير الافقي
xlApp.Windows(1).DisplayVerticalScrollBar = False 'إظهار وإخفاء شريط التمرير العمودي
xlApp.Windows(1).DisplayWorkbookTabs = False 'إظهار وإخفاء علامة تبويب الاوراق