مقدمة ▲
يصف هذا البرنامج التعليمي الاتصالات والتلاعب في مصنفات Excel مغلقة.
تحقق الدورات التدريبية المتاحة على Developpez.com ( الملفات وقواعد البيانات ) لمزيد من التفاصيل على نموذج ADO.
بعض المعلومات الواردة في هذه الوثيقة تأتي من على الانترنت تعليمات Microsoft .
ستشاهد في الأمثلة التالية التي الاستفسارات مماثلة لتلك المستخدمة في قواعد بيانات Access.
الفروق الوحيدة هي:
* محددة القيود اكسل (انظر الفصل الثاني).
* بناء الجملة الصدد (انظر الفصل الثالث).
* طريقة الجداول تحديد (علامات التبويب أو نطاقات الخلايا المسماة).
وبطبيعة الحال، Excel لا يمكن أن تتنافس مع وصول كقاعدة بيانات. ومع ذلك، يمكنك العمل على جداول البيانات في نفس الطريق. هذا مثير للاهتمام بشكل خاص عندما يكون لديك الكثير من البيانات والعديد من المصنفات للتعامل في إجراء واحد. في الوقت الذي وفره يمكن أن يكون ثم كبيرا.
المعلومات الواردة في هذه الوثيقة تفترض أن المصنف منظم كقاعدة بيانات حقيقية:
يستخدم السطر الأول للإشارة إلى اسم الحقل، من العمود الأول.
الحقول تتبع الممارسات الجيدة في أسماء التقارير:
* أقصر اسم ممكن.
* لا توجد مساحة.
* لا لهجة.
* لا أحرف خاصة.
أيضا تجنب المسافات والأحرف الخاصة في اسم من الأوراق.
ويمكن أن يكون الجدول جدول بيانات أو مجموعة من الخلايا المسماة.
يجب عليك أولا تمكين كائنات بيانات ActiveX Microsoft س س إشارة مكتبة لاستخدام الأمثلة في هذا البرنامج التعليمي.
في محرر ماكرو:
القائمة أدوات.
المراجع.
حدد خط "كائنات بيانات ActiveX Microsoft العشرون مكتبة".
انقر على زر موافق لتأكيد.
س س يعتمد على الإصدار المثبت على جهاز الكمبيوتر الخاص بك.
بعض الأمثلة المتوفرة لمعالجة الجداول وتحتاج إلى تفعيل إشارة مايكروسوفت ADO تحويلة س س ل DLL والأمن.
تحقق الدورات التدريبية المتاحة على Developpez.com ( الملفات وقواعد البيانات ) لمزيد من التفاصيل على نموذج ADO.
بعض المعلومات الواردة في هذه الوثيقة تأتي من على الانترنت تعليمات Microsoft .
ستشاهد في الأمثلة التالية التي الاستفسارات مماثلة لتلك المستخدمة في قواعد بيانات Access.
الفروق الوحيدة هي:
* محددة القيود اكسل (انظر الفصل الثاني).
* بناء الجملة الصدد (انظر الفصل الثالث).
* طريقة الجداول تحديد (علامات التبويب أو نطاقات الخلايا المسماة).
وبطبيعة الحال، Excel لا يمكن أن تتنافس مع وصول كقاعدة بيانات. ومع ذلك، يمكنك العمل على جداول البيانات في نفس الطريق. هذا مثير للاهتمام بشكل خاص عندما يكون لديك الكثير من البيانات والعديد من المصنفات للتعامل في إجراء واحد. في الوقت الذي وفره يمكن أن يكون ثم كبيرا.
المعلومات الواردة في هذه الوثيقة تفترض أن المصنف منظم كقاعدة بيانات حقيقية:
يستخدم السطر الأول للإشارة إلى اسم الحقل، من العمود الأول.
الحقول تتبع الممارسات الجيدة في أسماء التقارير:
* أقصر اسم ممكن.
* لا توجد مساحة.
* لا لهجة.
* لا أحرف خاصة.
أيضا تجنب المسافات والأحرف الخاصة في اسم من الأوراق.
ويمكن أن يكون الجدول جدول بيانات أو مجموعة من الخلايا المسماة.
يجب عليك أولا تمكين كائنات بيانات ActiveX Microsoft س س إشارة مكتبة لاستخدام الأمثلة في هذا البرنامج التعليمي.
في محرر ماكرو:
القائمة أدوات.
المراجع.
حدد خط "كائنات بيانات ActiveX Microsoft العشرون مكتبة".
انقر على زر موافق لتأكيد.
س س يعتمد على الإصدار المثبت على جهاز الكمبيوتر الخاص بك.
بعض الأمثلة المتوفرة لمعالجة الجداول وتحتاج إلى تفعيل إشارة مايكروسوفت ADO تحويلة س س ل DLL والأمن.
II. حدود إكسل يستخدم كقاعدة بيانات ▲
ويلخص هذا الفصل القيود والعمليات التي لا يمكن حملها في خزائن مغلقة.
وليس من الممكن حذف صفوف كاملة (سجلات) في مصنف مغلق.
سوف تحصل على رسالة خطأ "حذف البيانات في جدول مرتبط غير معتمد من قبل هذا ISAM."
يمكنك فقط حذف محتويات الخلايا.
لا يمكنك إزالة خطوط الفارغة التي تحتوي على البيانات وطلبات حذف تستمر لعرض سجلات فارغة المطابقة إلى هذه الصفوف الفارغة.
وليس من الممكن لتعديل خلية تحتوي على الصيغة.
سوف تحصل على رسالة خطأ "غير مسموح العملية المطلوبة في هذا السياق".
لا يمكن ل Excel التعامل مع وصلات متعددة في وقت واحد وعلى نفس المصنف.
قد يسبب الاستفسارات المتكررة مشاكل في الذاكرة في Excel.
انظر موقع مايكروسوفت للحصول على التفاصيل.
وليس من الممكن استخدام مصنف محمي بواسطة كلمة مرور.
وليس من الممكن استخدام الموثق إذا كانت ورقة تحتوي على البيانات محمي.
افتراضيا، برنامج تشغيل ODBC يحلل فقط 8 الصفوف الأولى من مصنف مغلق لتحديد نوع البيانات في كل عمود. هذا يمكن أن يسبب نوعين من المشاكل:
1. في حالات خاصة، البيانات التي تم تصديرها إلى مصنف مغلق يمكن أن يتم اقتطاعها.
إذا، على سبيل المثال، 8 السجلات الأولى لحقل يحتوي على النص بيانات أقل أو يساوي 255 حرفا، سيتم النظر في هذا الحقل نوع النص. إذا كنت ثم إضافة طول أطول من التسجيلات سيتم اقتطاع.
انظر موقع مايكروسوفت للحصول على التفاصيل.
2. إذا كنت ترغب في استيراد المعلومات في العمود الذي يحتوي على كل البيانات الرقمية والنص، ونوع الأغلبية في خطوط 8 الأولى التي تحدد نوع البيانات لاسترداد: أعمدة بيانات أخرى سوف تعتبر لاغية (فارغة).
4 إذا كان يحتوي على عمود القيم الرقمية وأربع قيم النص، الاستعلام بإرجاع 4 أرقام و 4 القيم الخالية.
الحل الوحيد هو تمكين خيار استيراد IMEX = 1. على سبيل المثال: "الخصائص الموسعة =" "إكسل 8.0، IMEX = 1" "".
سيتم استيراد البيانات الرقمية كنص.
يقتصر حجم جدول البيانات إلى 65536 الصفوف بواسطة 256 عمود ... انتظار Excel2007 ...؛ س)
محتويات الخلية (النص) يقتصر على 32،767 حرفا.
ويقتصر عدد الأوراق بالذاكرة المتوفرة.
وليس من الممكن حذف صفوف كاملة (سجلات) في مصنف مغلق.
سوف تحصل على رسالة خطأ "حذف البيانات في جدول مرتبط غير معتمد من قبل هذا ISAM."
يمكنك فقط حذف محتويات الخلايا.
لا يمكنك إزالة خطوط الفارغة التي تحتوي على البيانات وطلبات حذف تستمر لعرض سجلات فارغة المطابقة إلى هذه الصفوف الفارغة.
وليس من الممكن لتعديل خلية تحتوي على الصيغة.
سوف تحصل على رسالة خطأ "غير مسموح العملية المطلوبة في هذا السياق".
لا يمكن ل Excel التعامل مع وصلات متعددة في وقت واحد وعلى نفس المصنف.
قد يسبب الاستفسارات المتكررة مشاكل في الذاكرة في Excel.
انظر موقع مايكروسوفت للحصول على التفاصيل.
وليس من الممكن استخدام مصنف محمي بواسطة كلمة مرور.
وليس من الممكن استخدام الموثق إذا كانت ورقة تحتوي على البيانات محمي.
افتراضيا، برنامج تشغيل ODBC يحلل فقط 8 الصفوف الأولى من مصنف مغلق لتحديد نوع البيانات في كل عمود. هذا يمكن أن يسبب نوعين من المشاكل:
1. في حالات خاصة، البيانات التي تم تصديرها إلى مصنف مغلق يمكن أن يتم اقتطاعها.
إذا، على سبيل المثال، 8 السجلات الأولى لحقل يحتوي على النص بيانات أقل أو يساوي 255 حرفا، سيتم النظر في هذا الحقل نوع النص. إذا كنت ثم إضافة طول أطول من التسجيلات سيتم اقتطاع.
انظر موقع مايكروسوفت للحصول على التفاصيل.
2. إذا كنت ترغب في استيراد المعلومات في العمود الذي يحتوي على كل البيانات الرقمية والنص، ونوع الأغلبية في خطوط 8 الأولى التي تحدد نوع البيانات لاسترداد: أعمدة بيانات أخرى سوف تعتبر لاغية (فارغة).
4 إذا كان يحتوي على عمود القيم الرقمية وأربع قيم النص، الاستعلام بإرجاع 4 أرقام و 4 القيم الخالية.
الحل الوحيد هو تمكين خيار استيراد IMEX = 1. على سبيل المثال: "الخصائص الموسعة =" "إكسل 8.0، IMEX = 1" "".
سيتم استيراد البيانات الرقمية كنص.
يقتصر حجم جدول البيانات إلى 65536 الصفوف بواسطة 256 عمود ... انتظار Excel2007 ...؛ س)
محتويات الخلية (النص) يقتصر على 32،767 حرفا.
ويقتصر عدد الأوراق بالذاكرة المتوفرة.
III. أنواع الاتصال ▲
هناك نوعان من الاتصال متاحة للقراءة والكتابة في مصنف مغلق.
* مايكروسوفت جيت OLE DB.
* OLE DB برامج تشغيل.
* مايكروسوفت جيت OLE DB.
* OLE DB برامج تشغيل.
III-A. مايكروسوفت جيت OLE DB ▲
يقوم هذا المثال الأول إتصال بسيط المصنف ثم يغلق عليه باستخدام موفر Microsoft Jet OLE DB.
لديك فقط لضبط المسار واسم ملف Excel.
لديك فقط لضبط المسار واسم ملف Excel.
VBA
استخدم بناء الجملة التالية للاتصال إلى Excel 2007 المصنفات، وصيغ OPENXML XLSX و xlsm:
اختر
Sub
TestConnection_V1
(
) Dim
Cn As
ADODB .
Connection
Dim
Fichier As
String
'
Définit
le
classeur
fermé
servant
de
base
de
données
Fichier =
"
C:\monClasseurBase_V01.xls
"
Set
Cn =
New
ADODB .
Connection
'
---
Connexion
---
With
Cn .
Provider
=
"
Microsoft.Jet.OLEDB.4.0
"
.
ConnectionString
=
"
Data
Source=
"
&
Fichier &
_ "
;Extended
Properties=Excel
8.0;
"
.
Open
End
With
'
Extended
Properties=Excel
8.0
est
utilisé
pour
les
versions
d'Excel
97,
2000
et
2002.
'
'
...
la
requête
...
'
'
---
Fermeture
connexion
---
Cn .
Close
Set
Cn =
Nothing
End
Sub
ثالثا-B. OLE DB لبرامج تشغيل ODBC ▲
يستخدم الإجراء التالي موفر OLE DB برامج تشغيل لجعل الاتصال.
VBA
اختر
Sub
TestConnection_V2
(
) Dim
Cn As
ADODB .
Connection
Dim
Fichier As
String
'
Définit
le
classeur
fermé
servant
de
base
de
données
Fichier =
"
C:\monClasseurBase_V01.xls
"
Set
Cn =
New
ADODB .
Connection
With
Cn .
Provider
=
"
MSDASQL
"
.
ConnectionString
=
"
Driver={Microsoft
Excel
Driver
(*.xls)};
"
&
_ "
DBQ=
"
&
Fichier &
"
;
ReadOnly=False;
"
.
Open
End
With
'
'
...
la
requête
...
'
'
---
Fermeture
connexion
---
Cn .
Close
Set
Cn =
Nothing
End
Sub
III-C. الاتصال المصنفات Excel2007 XLSX وو xlsm ▲
VBA
اختر
Sub
RequeteClasseurFerme_Excel2007
(
) Dim
Cn As
ADODB .
Connection
Dim
Fichier As
String
Dim
NomFeuille As
String
, texte_SQL As
String
Dim
Rst As
ADODB .
Recordset
'
Définit
le
classeur
fermé
servant
de
base
de
données
Fichier =
"
C:\Documents
and
Settings\mimi\dossier\NomClasseur.xlsx
"
'
Nom
de
la
feuille
dans
le
classeur
fermé
NomFeuille =
"
Feuil1
"
Set
Cn =
New
ADODB .
Connection
'
---
Connexion
---
With
Cn .
Provider
=
"
Microsoft.Jet.OLEDB.4.0
"
.
ConnectionString
=
"
Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=
"
_ &
Fichier &
"
;Extended
Properties=
"
"
Excel
12.0;HDR=YES;
"
"
"
.
Open
End
With
'
-----------------
'
'
...
la
requête
...
'
'
---
Fermeture
connexion
---
Cn .
Close
Set
Cn =
Nothing
End
Sub
الرابع. استفسار ▲
IV-A. قراءة ▲
قبل أن تقفز في استكشاف نموذج ADO، تذكر أن Excel ديه 2 أدوات لقراءة في مصنف مغلق:
الصيغ الرابط:
الصيغ الرابط:
اختر
='C:\Documents and Settings\mimi\dossier\excel\[ClasseurBase.xls]Feuil1'!$A$1
اختر
'Faire une RECHERCHE Verticale dans un classeur fermé: 'Rechercher "DVP" dans la colonne A du classeur fermé et afficher la donnée correspondante de la colonne B. =RECHERCHEV("DVP";'C:\Documents and Settings\mimi\dossier\excel\[ClasseurBase.xls]Feuil1'!$A:$B;2;FAUX)
وحدات الماكرو Excel4:
VBA
اختر
'
Lecture
de
la
cellule
A1
dans
la
Feuil1
du
classeur
fermé
MsgBox
ExecuteExcel4Macro
(
"
'C:\Documents
and
Settings\mimi\dossier\excel\[ClasseurBase.xls]Feuil1'!R1C1
"
)
لا يمكن ل Excel المنافسة مع وصول ولكن تظهر الأمثلة التالية التي من الممكن التعامل مع المصنفات على نفس المبدأ.
هنا هو ماكرو الذي يسمح لك لربط مصنف لاستخراج محتويات الورقة المسماة "SHEET1".
VBA
اختر
Sub
RequeteClasseurFerme
(
) Dim
Cn As
ADODB .
Connection
Dim
Fichier As
String
Dim
NomFeuille As
String
, texte_SQL As
String
Dim
Rst As
ADODB .
Recordset
'
Définit
le
classeur
fermé
servant
de
base
de
données
Fichier =
"
C:\monClasseurBase.xls
"
'
Nom
de
la
feuille
dans
le
classeur
fermé
NomFeuille =
"
Feuil1
"
Set
Cn =
New
ADODB .
Connection
'
---
Connection
---
With
Cn .
Provider
=
"
Microsoft.Jet.OLEDB.4.0
"
.
ConnectionString
=
"
Data
Source=
"
&
Fichier &
_ "
;Extended
Properties=Excel
8.0;
"
.
Open
End
With
'
-----------------
'
Définit
la
requête.
'
/!\
Attention
à
ne
pas
oublier
le
symbole
$
après
le
nom
de
la
feuille.
texte_SQL =
"
SELECT
*
FROM
[
"
&
NomFeuille &
"
$]
"
Set
Rst =
New
ADODB .
Recordset
Set
Rst =
Cn .
Execute
(
texte_SQL) '
Ecrit
le
résultat
de
la
requête
dans
la
cellule
A2
Range
(
"
A2
"
) .
CopyFromRecordset
Rst '
---
Fermeture
connexion
---
Cn .
Close
Set
Cn =
Nothing
End
Sub
إضافة المعلمة HDR = NO إذا كنت ترغب في استرداد محتويات السطر الأول. وتعتبر هذه واحدة من ضربة رأس (أو حقل)، افتراضيا.
"، الموسعة خصائص =" "إكسل 8.0، HDR = NO" ""
منذ يعتبر السطر الأول من ضربة رأس، فمن الممكن أيضا أن حلقة على أسماء الحقول من أجل استخراج المحتويات:
VBA
اختر
Dim
i As
Integer '
'
'
...
'
'
Définit
la
requête.
texte_SQL =
"
SELECT
*
FROM
[
"
&
NomFeuille &
"
$]
"
Set
Rst =
New
ADODB .
Recordset
Set
Rst =
Cn .
Execute
(
texte_SQL) '
---
Boucle
sur
les
entêtes
pour
récupérer
les
noms
---
For
i =
0
To
Rst .
Fields
.
Count
-
1
Cells
(
1
, i +
1
) =
Rst .
Fields
(
i) .
Name
Next
i '
------------------------------------------------------
'
Ecrit
le
résultat
de
la
requête
dans
la
cellule
A2
Range
(
"
A2
"
) .
CopyFromRecordset
Rst '
'
...
'
ملاحظة:
الأسلوب CopyFromRecordset لعرض نتيجة مجموعة السجلات في ورقة العمل من خلايا محددة (A2 في المثال أعلاه).
إدارة الجدول:
عند كتابة الاستفسارات، يجب إضافة رمز $ بعد اسم من علامات التبويب (الجداول)، وهي ليست حالة نطاقات الخلايا المسماة (تعتبر أيضا الجداول).
من سلبيات، إذا قمت بإضافة الجدول بشكل حيوي في الموثق (على سبيل المثال باستخدام "إنشاء جدول" أو "SELECT INTO" كما في الفصل الرابع-D)، يتم إرجاع اسمين لهذا الجدول الجديد: مع وبدون $ . في الواقع إذا قمت بفتح المصنف يدويا سترى أن تتم إضافة علامة التبويب، فضلا عن مجموعة من الخلايا المسماة الموافق البيانات المدرجة بشكل حيوي (انظر إدراج / اسم / القائمة حدد): على سبيل المثال = maNouvelleFeuille $ A $ 1: $ C $ 1265.
يجب وضع اسم من الأوراق بين قوسين معقوفين في الاستعلامات [ "SheetName وو" $] ".
يمكنك أيضا أن تلعب خلية معينة أو مجموعة من الخلايا، وذلك باستخدام أسلوب ADO.
VBA
اختر
Sub
extractionValeurCelluleClasseurFerme
(
) Dim
Source As
ADODB .
Connection
Dim
Rst As
ADODB .
Recordset
Dim
ADOCommand As
ADODB .
Command
Dim
Fichier As
String
, Cellule As
String
, Feuille As
String
'
Adresse
de
la
cellule
contenant
la
donnée
à
récupérer
Cellule =
"
B4:B4
"
'
Pour
une
plage
de
cellules,
utilisez:
'
Cellule
=
"A4:C10"
Feuille =
"
Feuil1$
"
'
n'oubliez
pas
d'ajouter
$
au
nom
de
la
feuille.
'
Chemin
complet
du
classeur
fermé
Fichier =
"
C:\Base.xls
"
Set
Source =
New
ADODB .
Connection
Source .
Open
"
Provider=Microsoft.Jet.OLEDB.4.0;
"
&
_ "
Data
Source=
"
&
Fichier &
"
;Extended
Properties=
"
"
Excel
8.0;HDR=No;
"
"
;
"
Set
ADOCommand =
New
ADODB .
Command
With
ADOCommand .
ActiveConnection
=
Source .
CommandText
=
"
SELECT
*
FROM
[
"
&
Feuille &
Cellule &
"
]
"
End
With
Set
Rst =
New
ADODB .
Recordset
Rst .
Open
ADOCommand, , adOpenKeyset
, adLockOptimistic
Set
Rst =
Source .
Execute
(
"
[
"
&
Feuille &
Cellule &
"
]
"
) Range
(
"
A2
"
) .
CopyFromRecordset
Rst Rst .
Close
Source .
Close
Set
Source =
Nothing
Set
Rst =
Nothing
Set
ADOCommand =
Nothing
End
Sub
يوضح هذا المثال الأخير كيفية تنفيذ صلة بين لوحين من مصنف مغلق.
إجراء مقارنة عمودين في علامات تبويب مختلفة، ويسرد البيانات المشتركة.
يحتوي SHEET1 عمود الذي يسمى NumPeriode1 الرأس.
يحتوي على SHEET2 عمود الذي يسمى NumPeriode2 الرأس.
قائمة البيانات الاقتصادية من SHEET2 التي تظهر أيضا في SHEET1.
VBA
اختر
Sub
requeteJointure_ControleDoublons
(
) Dim
Source As
ADODB .
Connection
Dim
Requete As
ADODB .
Recordset
Dim
Fichier As
String
, xSQL As
String
Dim
i As
Long Fichier =
"
C:\Base.xls
"
Set
Source =
New
ADODB .
Connection
Source .
Open
"
Provider
=
Microsoft.Jet.OLEDB.4.0;
"
&
_ "
data
source=
"
&
Fichier &
"
;
"
&
_ "
extended
properties=
"
"
Excel
8.0;HDR=Yes
"
"
"
xSQL =
"
SELECT
DISTINCT
[Feuil2$].NumPeriode2
"
&
"
FROM
[Feuil2$]
"
&
_ "
INNER
JOIN
[Feuil1$]
ON
[Feuil2$].NumPeriode2
=
[Feuil1$].NumPeriode1
"
Set
Requete =
New
ADODB .
Recordset
Set
Requete =
Source .
Execute
(
xSQL) If
Requete .
EOF
Then
MsgBox
"
Il
n'y
a
pas
de
doublons.
"
Else
'
MsgBox
"il
y
a
des
doublons."
Range
(
"
A2
"
) .
CopyFromRecordset
Requete End
If
Requete .
Close
Source .
Close
End
Sub
رابعا-B. اضافة رد ▲
ويبين هذا الفصل كيفية إضافة سجلات، في أعقاب البيانات الموجودة في مصنف منظم كقاعدة بيانات. يستخدم السطر الأول للإشارة إلى اسم الحقل، من العمود الأول (A)، ومن ثم تضاف إلى سجلات تلقائيا في الأسطر التالية.
سيتم إدراج البيانات كنص إذا لم يتم تحديد شكل خلايا في الملف المرفق.
ويضيف هذا المثال رقما قياسيا في SHEET1 يحتوي على 4 حقول (تاريخ، النص، النص الرقمي).
سيتم إدراج البيانات كنص إذا لم يتم تحديد شكل خلايا في الملف المرفق.
ويضيف هذا المثال رقما قياسيا في SHEET1 يحتوي على 4 حقول (تاريخ، النص، النص الرقمي).
VBA
اختر
Sub
ajoutEnregistrement
(
) Dim
Cn As
ADODB .
Connection
Dim
Fichier As
String
, Feuille As
String
, strSQL As
String
Dim
LaDate As
Date
Dim
PrixUnit As
Integer Dim
leNom As
String
, lePrenom As
String
Fichier =
"
C:\Base.xls
"
Feuille =
"
Feuil1
"
'
Les
données
à
insérer:
LaDate =
CDate
(
"
26/05/2006
"
) leNom =
"
NomTest
"
lePrenom =
"
PrenomTest
"
PrixUnit =
40
Set
Cn =
New
ADODB .
Connection
With
Cn .
Provider
=
"
MSDASQL
"
.
ConnectionString
=
"
Driver={Microsoft
Excel
Driver
(*.xls)};
"
&
_ "
DBQ=
"
&
Fichier &
"
;
ReadOnly=False;
"
.
Open
End
With
'
Les
données
doivent
être
indiquées
dans
le
même
ordre
que
les
champs
dans
la
base
de
données.
strSQL =
"
INSERT
INTO
[
"
&
Feuille &
"
$]
"
_ &
"
VALUES
(#
"
&
LaDate &
"
#,
"
&
_ "
'
"
&
leNom &
"
',
"
&
_ "
'
"
&
lePrenom &
"
',
"
&
_ PrixUnit &
"
)
"
Cn .
Execute
strSQL Cn .
Close
Set
Cn =
Nothing
End
Sub
كما يسمح هذا النموذج ADO منك أن تكتب في خلية معينة:
لإدراج الماكرو التالي النص في الخلية G30 أغلق المصنف.
VBA
اختر
Sub
exportDonneeDansCelluleClasseurFerme
(
) Dim
Cn As
ADODB .
Connection
Dim
Cd As
ADODB .
Command
Dim
Rst As
ADODB .
Recordset
Dim
Fichier As
String
Fichier =
"
C:\Documents
and
Settings\mimi\dossier\LeClasseur.xls
"
Set
Cn =
New
ADODB .
Connection
Cn .
Open
"
Provider=Microsoft.Jet.OLEDB.4.0;
"
&
_ "
Data
Source=
"
&
Fichier &
"
;
"
&
_ "
Extended
Properties=
"
"
Excel
8.0;HDR=No;
"
"
;
"
Set
Cd =
New
ADODB .
Command
Cd .
ActiveConnection
=
Cn Cd .
CommandText
=
"
SELECT
*
FROM
[Feuil1$G30:G30]
"
Set
Rst =
New
ADODB .
Recordset
Rst .
Open
Cd, , adOpenKeyset
, adLockOptimistic
Rst
(
0
) .
Value
=
"
Donnée
test
"
Rst .
Update
Cn .
Close
Set
Cn =
Nothing
Set
Cd =
Nothing
Set
Rst =
Nothing
End
Sub
رابعا-C. تحرير التسجيلات ▲
في بنفس الطريقة التي في Access، يمكنك إجراء تحديثات على قاعدة البيانات باستخدام الاستعلام UPDATE.
ويستكمل هذا المثال قيمة "Champ4" إذا كان "Field2" هو متغير "مربع اسم".
ويستكمل هذا المثال قيمة "Champ4" إذا كان "Field2" هو متغير "مربع اسم".
VBA
اختر
Sub
miseAJour_Enregistrement
(
) Dim
Cn As
ADODB .
Connection
Dim
Fichier As
String
, Feuille As
String
, strSQL As
String
Dim
PrixUnit As
Integer Dim
leNom As
String
Fichier =
"
C:\Base.xls
"
Feuille =
"
Feuil1
"
leNom =
"
NomTest
"
PrixUnit =
45
Set
Cn =
New
ADODB .
Connection
With
Cn .
Provider
=
"
MSDASQL
"
.
ConnectionString
=
"
Driver={Microsoft
Excel
Driver
(*.xls)};
"
&
_ "
DBQ=
"
&
Fichier &
"
;
ReadOnly=False;
"
.
Open
End
With
'
Met
à
jour
la
valeur
du
"Champ4"
si
le
"Champ2"
correspond
à
la
variable
"leNom"
strSQL =
"
UPDATE
[
"
&
Feuille &
"
$]
SET
"
&
_ "
Champ4
=
"
&
PrixUnit &
"
WHERE
Champ2
=
'
"
&
leNom &
"
'
"
Cn .
Execute
strSQL Cn .
Close
Set
Cn =
Nothing
End
Sub
IV-D. إضافة ورقة في مصنف مغلق ▲
ويمكن خلق ديناميكية ورقة عمل جديدة في مصنف مغلق باستخدام نموذج ADO.
هذا ليس شيئا المعتاد جدا ولكن هو في المقام الأول هنا لإظهار الإمكانيات المرتبطة مع Excel.
هذا المثال يوضح كيفية إضافة ورقة جديدة في إغلاق ملف Excel ونقل محتويات استعلام في جدول Access.
هذا ليس شيئا المعتاد جدا ولكن هو في المقام الأول هنا لإظهار الإمكانيات المرتبطة مع Excel.
هذا المثال يوضح كيفية إضافة ورقة جديدة في إغلاق ملف Excel ونقل محتويات استعلام في جدول Access.
VBA
اختر
Sub
tranfertTableAccess_Vers_ClasseurExcelFerme
(
) '
Transfére
une
Table
Access
dans
un
nouvel
onglet
d'un
classeur
fermé.
'
Dim
ExcelCn As
ADODB .
Connection
Dim
ExcelRst As
ADODB .
Recordset
Dim
AccessCn As
New
ADODB .
Connection
Dim
AccessRst As
New
ADODB .
Recordset
Dim
maBase As
String
, maFeuille As
String
, listeTable As
String
Dim
maTable As
String
, NomClasseur As
String
Dim
j As
Integer Dim
Fld As
ADODB .
Field
'
Chemin
de
la
base
Access
maBase =
"
C:\Documents
and
Settings\mimi\dossier\dataBase.mdb
"
'
Nom
de
la
table
Access
à
transfèrer
maTable =
"
Table1
"
'
Classeur
ou
va
être
créée
la
nouvelle
feuille
NomClasseur =
"
C:\leClasseurFermé.xls
"
'
Nom
de
la
nouvelle
feuille
Excel
maFeuille =
"
MaNouvelleFeuille
"
'
Connection
à
la
base
Access
AccessCn .
Open
"
provider=microsoft.jet.oledb.4.0;
data
source=
"
&
maBase '
Requète
dans
la
table
Access
AccessRst .
Open
"
SELECT
*
FROM
"
&
maTable, AccessCn, adOpenStatic
'
Connection
au
classeur
Excel
Set
ExcelCn =
New
ADODB .
Connection
ExcelCn .
Open
"
Provider=Microsoft.Jet.OLEDB.4.0;
"
&
_ "
Data
Source=
"
&
NomClasseur &
"
;
"
&
_ "
Extended
Properties=
"
"
Excel
8.0;HDR=NO;
"
"
"
'
paramétrage
des
entêtes
et
types
de
données
For
Each
Fld In
AccessRst .
Fields
listeTable =
listeTable &
Fld .
Name
&
"
"
&
FieldType
(
Fld .
Type
) &
"
,
"
Next
Fld '
création
nouvelle
Feuille
Excel
listeTable =
Left
(
listeTable, Len
(
listeTable) -
1
) ExcelCn .
Execute
"
create
table
"
&
maFeuille &
"
(
"
&
listeTable &
"
)
"
Set
ExcelRst =
New
ADODB .
Recordset
ExcelRst .
Open
"
Select
*
from
"
&
maFeuille, ExcelCn, adOpenKeyset
, adLockOptimistic
'
transfert
les
données
Access
vers
le
classeur
Excel
Do
While
Not
(
AccessRst .
EOF
) ExcelRst .
AddNew
For
j =
0
To
ExcelRst .
Fields
.
Count
-
1
ExcelRst .
Fields
(
j) =
AccessRst .
Fields
(
j) .
Value
Next
j ExcelRst .
Update
AccessRst .
MoveNext
Loop
AccessRst .
Close
AccessCn .
Close
Set
ExcelRst =
Nothing
Set
ExcelCn =
Nothing
End
Sub
VBA
اختر
Function
FieldType
(
Valeur As
Long) As
String
'
Spécification
des
types
de
données
pour
la
création
des
champs
Excel.
'
Attention
!
la
liste
est
incomplète.
'
Select
Case
Valeur Case
6
FieldType =
"
currency
"
Case
7
, 133
, 134
, 135
FieldType =
"
Date
"
Case
14
, 131
FieldType =
"
Decimal
"
Case
5
FieldType =
"
Float
"
Case
3
, 2
FieldType =
"
Integer
"
Case
4
FieldType =
"
Real
"
Case
200
, 202
FieldType =
"
Text
"
Case
11
FieldType =
"
Boolean
"
Case
203
FieldType =
"
Memo
"
Case
16
FieldType =
"
Tinyint
"
End
Select
End
Function
والحل الثاني إلى نفس النتيجة:
VBA
اختر
Sub
tranfertTableAccess_Vers_ClasseurExcelFerme_V02
(
) '
Transfére
une
Table
Access
dans
un
nouvel
onglet
d'un
classeur
fermé
'
Dim
ExcelCn As
ADODB .
Connection
Dim
ExcelRst As
ADODB .
Recordset
Dim
AccessCn As
New
ADODB .
Connection
Dim
AccessRst As
New
ADODB .
Recordset
Dim
maBase As
String
, maFeuille As
String
Dim
maTable As
String
, NomClasseur As
String
Dim
nbEnr As
Long '
Chemin
de
la
base
Access
maBase =
"
C:\Documents
and
Settings\mimi\dossier\dataBase.mdb
"
'
Nom
de
la
table
Access
à
transfèrer
maTable =
"
Table1
"
'
Classeur
dans
lequel
va
être
créée
la
nouvelle
feuille
NomClasseur =
"
C:\leClasseurFermé.xls
"
'
Nom
de
la
nouvelle
feuille
Excel
maFeuille =
"
MaNouvelleFeuille2
"
'
Connection
à
la
base
Access
AccessCn .
Open
"
provider=microsoft.jet.oledb.4.0;
data
source=
"
&
maBase '
Requète
dans
la
table
Access
AccessRst .
Open
"
SELECT
*
FROM
"
&
maTable, AccessCn, adOpenStatic
'
Connection
au
classeur
Excel
Set
ExcelCn =
New
ADODB .
Connection
ExcelCn .
Open
"
Provider=Microsoft.Jet.OLEDB.4.0;
"
&
_ "
Data
Source=
"
&
NomClasseur &
"
;
"
&
_ "
Extended
Properties=
"
"
Excel
8.0;HDR=NO;
"
"
"
'
Transfert
les
données
d'Access
vers
Excel
AccessCn .
Execute
"
SELECT
*
INTO
[Excel
8.0;
"
&
_ "
Database=
"
&
NomClasseur &
"
].[
"
&
maFeuille &
"
]
FROM
"
&
maTable, nbEnr AccessRst .
Close
AccessCn .
Close
Set
ExcelRst =
Nothing
Set
ExcelCn =
Nothing
End
Sub
خامسا انظر بنية الجدول ▲
إذا كنت تعمل على ملفات اكسل مغلقة، قد تحتاج إلى استرداد المعلومات حول
هيكلها: إدراج أسماء الأوراق، تحقق ما إذا كان ورقة محددة هي، والسيطرة على
تنسيق حقل ... الخ ... ADO و ADOX نماذج تسمح للحصول على هذه البيانات.
جميع النطاقات علامات التبويب والخلية المسماة يعتبروا الجداول.
يمكنك سرد أسمائهم باستخدام الماكرو التالي.
جميع النطاقات علامات التبويب والخلية المسماة يعتبروا الجداول.
يمكنك سرد أسمائهم باستخدام الماكرو التالي.
VBA
اختر
Sub
Liste_Feuilles_PlagesNommees_ClasseurFerme_V01
(
) '
Nécéssite
d'activer
la
référence
Microsoft
ADO
ext
xx
for
DLL
and
Security
'
Nécéssite
d'activer
la
référence
Microsoft
ActiveX
Data
Objects
xx
Library
Dim
Cn As
ADODB .
Connection
Dim
oCat As
ADOX .
Catalog
Dim
Fichier As
String
, Resultat As
String
Dim
Feuille As
ADOX .
Table
Fichier =
"
C:\Base.xls
"
Set
Cn =
New
ADODB .
Connection
Set
oCat =
New
ADOX .
Catalog
Cn .
Open
"
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=
"
&
Fichier &
_ "
;Extended
Properties=Excel
8.0;
"
Set
oCat .
ActiveConnection
=
Cn For
Each
Feuille In
oCat .
Tables
Resultat =
Resultat &
Feuille .
Name
&
vbCrLf
Next
MsgBox
Resultat Set
Feuille =
Nothing
Set
oCat =
Nothing
Cn .
Close
Set
Cn =
Nothing
End
Sub
وصف نتيجة الماكرو:
يتم إرجاع * الأسماء في الترتيب الأبجدي.
* اسم أوراق يليه رمز $، وهي ليست حالة اسمه الخلايا.
* اسم أوراق إنشاؤه بشكل حيوي (انظر الفصل الرابع-D) لا تملك الرمز $.
* يتم إرجاع اسم الورقة إنشاؤه بشكل حيوي 2 مرات: من علامة التبويب ونطاق مسمى يرتبط.
* يتم مؤطرة الأسماء التي تحتوي على مسافات من الاقتباس ".
هنا هو الطريقة الثانية لسرد أسماء الجداول.
VBA
اختر
Sub
Liste_Feuilles_PlagesNommees_ClasseurFerme_V02
(
) '
Nécéssite
d'activer
la
référence
Microsoft
ActiveX
Data
Objects
xx
Library
Dim
Cn As
ADODB .
Connection
Dim
rsT As
ADODB .
Recordset
Dim
Resultat As
String
, Fichier As
String
Fichier =
"
C:\Base.xls
"
Set
Cn =
New
ADODB .
Connection
With
Cn .
Provider
=
"
Microsoft.Jet.OLEDB.4.0
"
.
ConnectionString
=
"
Data
Source=
"
&
Fichier &
_ "
;Extended
Properties=Excel
8.0;
"
.
Open
End
With
Set
rsT =
Cn .
OpenSchema
(
adSchemaTables) While
Not
rsT .
EOF
Resultat =
Resultat &
rsT .
Fields
(
"
TABLE_NAME
"
) &
vbCrLf
rsT .
Fields
(
"
TABLE_NAME
"
) rsT .
MoveNext
Wend
MsgBox
Resultat rsT .
Close
Set
rsT =
Nothing
Cn .
Close
Set
Cn =
Nothing
End
Sub
يمكنك أيضا البحث عن ورقة محددة.
VBA
اختر
Sub
VerifierExistenceFeuille
(
) '
Nécéssite
d'activer
la
référence
Microsoft
ADO
ext
xx
for
DLL
and
Security
'
Nécéssite
d'activer
la
référence
Microsoft
ActiveX
Data
Objects
xx
Library
Dim
Cn As
ADODB .
Connection
Dim
oCat As
ADOX .
Catalog
Dim
Fichier As
String
Dim
Feuille As
ADOX .
Table
Fichier =
"
C:\Base.xls
"
Set
Cn =
New
ADODB .
Connection
Set
oCat =
New
ADOX .
Catalog
Cn .
Open
"
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=
"
&
Fichier &
_ "
;Extended
Properties=Excel
8.0;
"
Set
oCat .
ActiveConnection
=
Cn On
Error
Resume
Next
'
Vérifie
si
la
feuille
"Feuil1"
existe
dans
le
classeur
fermé
Set
Feuille =
oCat .
Tables
(
"
Feuil1$
"
) On
Error
GoTo
0
If
Feuille Is
Nothing
Then
MsgBox
"
La
feuille
n'existe
pas.
"
Else
MsgBox
"
La
feuille
existe.
"
End
If
Set
Feuille =
Nothing
Set
oCat =
Nothing
Cn .
Close
Set
Cn =
Nothing
End
Sub
هذا المثال الأخير، من مساعدة مايكروسوفت، يتيح لك عرض بنية مصنف مغلق.
وضع الداخلي في UserForm وإضافة مربع القائمة المسمى "ListBox1".
VBA
اختر
Private
Sub
UserForm_Initialize
(
) '
'
Source:
http://www.kbalertz.com/kb_Q257819.aspx#RetrieveMetadata
'
Dim
Cn As
ADODB .
Connection
Dim
Rst As
ADODB .
Recordset
, Rsc As
ADODB .
Recordset
Dim
intTblCnt As
Integer, intTblFlds As
Integer Dim
strTbl As
String
, strCol As
String
Dim
intColCnt As
Integer, intColFlds As
Integer Dim
Fichier As
String
Dim
t As
Integer, c As
Integer, f As
Integer Fichier =
"
C:\Base.xls
"
Set
Cn =
New
ADODB .
Connection
With
Cn .
Provider
=
"
MSDASQL
"
.
ConnectionString
=
"
Driver={Microsoft
Excel
Driver
(*.xls)};
"
&
_ "
DBQ=
"
&
Fichier &
"
;
"
.
Open
End
With
Set
Rst =
Cn .
OpenSchema
(
adSchemaTables) intTblCnt =
Rst .
RecordCount
intTblFlds =
Rst .
Fields
.
Count
ListBox1 .
AddItem
"
Tables:
"
&
intTblCnt ListBox1 .
AddItem
"
--------------------
"
For
t =
1
To
intTblCnt strTbl =
Rst .
Fields
(
"
TABLE_NAME
"
) .
Value
ListBox1 .
AddItem
vbTab
&
"
Table
#
"
&
t &
"
:
"
&
strTbl ListBox1 .
AddItem
vbTab
&
"
--------------------
"
For
f =
0
To
intTblFlds -
1
ListBox1 .
AddItem
vbTab
&
Rst .
Fields
(
f) .
Name
&
_ vbTab
&
Rst .
Fields
(
f) .
Value
Next
ListBox1 .
AddItem
"
--------------------
"
Set
Rsc =
Cn .
OpenSchema
(
adSchemaColumns, Array
(
Empty
, Empty
, strTbl, Empty
)) intColCnt =
Rsc .
RecordCount
intColFlds =
Rsc .
Fields
.
Count
For
c =
1
To
intColCnt strCol =
Rsc .
Fields
(
"
COLUMN_NAME
"
) .
Value
ListBox1 .
AddItem
vbTab
&
vbTab
&
"
Column
#
"
&
c &
"
:
"
&
strCol ListBox1 .
AddItem
vbTab
&
vbTab
&
"
--------------------
"
For
f =
0
To
intColFlds -
1
ListBox1 .
AddItem
vbTab
&
vbTab
&
Rsc .
Fields
(
f) .
Name
&
_ vbTab
&
Rsc .
Fields
(
f) .
Value
Next
ListBox1 .
AddItem
vbTab
&
vbTab
&
"
--------------------
"
Rsc .
MoveNext
Next
Rsc .
Close
Set
Rsc =
Nothing
ListBox1 .
AddItem
"
--------------------
"
Rst .
MoveNext
Next
Rst .
Close
Set
Rst =
Nothing
Cn .
Close
Set
Cn =
Nothing
End
Sub
السادس. تحميل ▲
ح
Aucun commentaire:
Enregistrer un commentaire