????? ??????? ADO ?? ?????? Excel ?? Visual Basic ?? VBA

?????? ????????? ?????? ?????????
???? ???????: 257819 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

?????? ??? ??????? ??????? ?????? ?????? ActiveX (ADO) ?? ????? ?????? Microsoft Excel ????? ??????. ??????? ???? ????? ??????? ???? ?????? ??????? ?????? ? Excel. ??? ??????? ????? ?????? OLAP ?? PivotTable ?? ????????? ???? ???? ?? Excel ??????.

??????? ?????????? ???? ??? ??? ??????? ?????? ?????? ?? ????? ??????? ? Microsoft:
303814 ????? ??????? ADOX ?? ?????? Excel ?? Visual Basic ?? VBA

??????? ????

????? ???

???? ?????? ?? ???? ?????? Microsoft Excel ?? ??? ???? ???? ?????? ?? ???? ????? ??????. ????? ?? ???????? ??????????? ?? ???? ?? Microsoft Excel ??? ???? ????? ????? ?????? ???????? ? ?????? ??? ?????? ???? ?????? ??? ???????? ???? ?????? ?? ???? ?? ????????? ?? Excel ???????? ?????? ???????? ????????.

Microsoft ?????? ?????? ActiveX ???? ?? ?????? ??????? ?? ???? Excel ??? ?? ???? ????? ??????. ?????? ??? ??????? ????? ????? ??? ?? ?? ??? ???????: ??????: ???????? ???? ??????? ???? ?? ?????? Microsoft Access Components (MDAC) 2.5 ?? ???? ??????? Microsoft Windows 2000 ???????? Visual Basic 6.0 ???? ?????? service Pack 3 ? Excel 2000. ?? ????? ??? ??????? ?? ?????? ?????????? ?? ???? ?? ????? ?????????? ?? ????????? ???????? ? MDAC? Microsoft Windows ?? Visual Basic ?? Excel.

??????? ??? Excel ???????? ADO

??????? ???? ?????? Excel ???????? ?? ?? ????? OLE ADO ????? ????? ?????? ???? ?? MDAC:
  • ???? Microsoft Jet OLE DB-??-

  • ???? Microsoft OLE DB ?????? ????? ODBC

????? ??????? ???? Microsoft Jet OLE DB

????? ???? Jet ??? ????? ?? ????????? ???????? ??????? ????? ?????? Excel: ??????? ??? ?? ??? ??? ?????? ? ????? ??? Excel.

??????? ????? ????? ???? jet
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "Microsoft.Jet.OLEDB.4.0"
	.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
	.Open
End With
				
????? ??????: ?? ??????? ??????? ???? Jet 4.0; ????? ?? ???? ???? jet 3.51 ????? ????? Jet ISAM. ??? ??? ?????? ??????? ?????? Jet 3.51 ???? ?? ??? ??????? ????? ????? ????? ???????:
???? ?????? ??? ISAM ?????? ???????.
??????? ?? Excel: ????? Excel 5.0 ????? Excel 95 (??????? 7.0 Excel)? ? Excel 8.0 Excel 97 ?? Excel 2000 ?? Excel 2002 (XP) ?????? (????????? 8.0 ? 9.0 10.0 ?? Excel).

???? jet ???????? ???? ?????? ????? ?????? ????????

??? ??? ?????? ???? ???? ?????? ADO ?? ???? ???????? ?? ??????? ????? ??? ?? ???? ?????? ????? ?????? ???????? ?????? ???? ??????? ???????? ???????.
  1. ?? ????? ??????? ???? ? ??? ???? Jet 4.0; ?? ??? ???? ???? Jet 3.51 ???? ????? ????? Jet ISAM. ??? ??? ?????? ???? Jet 3.51? ?? ??? ??????? ????? ????? ????? ???????:
    ???? ?????? ??? ISAM ?????? ???????.
  2. ??? ????? ??????? ????? ? ?????? ??? ??? ?????? ????? ??. ????? "???? ????????" ? ??????? "???? ??????"? ??? ??? ?? ????? ??? ????? Excel. (????? ?? ???? ??? ??? ???? ????? ???? Excel ????? ??????. ???? ?????? ??????? ??? ??? ??????? ?????? ?? ??? ???????.)
  3. ?? ????? ??????? ???? ? ??? ??????? ??????? ?? ???????? ??? ?? ???? ??? ????? ????. ?? ?????? Excel 8.0; ?????? ?? ??? ??????? ???? ?????? ?????? ?????? (;). ??? ???? ??? ??????? ????? ????? ????? ???? ??? ?????? ????? ?? ???????? ??? ???? Jet ????? ?????? Microsoft Access ????? ??? ??? ????? ????? ???? ???.
  4. ???? ??? ????? ??????? ????? ????? ??? ?????? ???????. ???? ?? ???? ???? ????? ????? ??? ??????? ?????.
??????? ????? ???? Jet ????

?????? ???????: ?????????? ??? ??????? ?? ???? ????? ?? ???? ???????? Excel ????? ??? ?????? ??????? ???? ???? ????????? ???? ?????. ??? ?? ??? ??? ??????? ??? ????? ??? ???????? ?? ???? ????? ?? ?? ???????? "?????" ?????????? ?????? ????. ??? ??? ?? ???? ????? ??????? HDR = ??????? ?? ??????? ??????? ?????? ???????. ?????????? ??? ????? ?? ???? ?????? ??? HDR = ???. ??? ?? ??? ???? ?????? ???????? ??? ????? HDR = ??; ?????? ????? ?????? F1? F2? ???. ?? ????? ??????? ??????? ???? ????? ??? ??? ??????? ???? ??????? ?? ?????? ??????? ???????? ????? ???????? ??? ?????? ??? ?? ?????? ???????? ???????? ?????? Visual ???????? ??????? ?? ??? ?????? ?? ?????? ???????? ???? ????? ?? ????? ?????? ??? ???? ?????? (??? ?????? ?????? ??? ??????? ?????? ??????).
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=" " Excel 8.0; HDR=No;" " "
				

??????? ???? Microsoft OLE DB ?????? ????? ODBC

???? ????? ????? ODBC (???? ???? ??? ??? ??????? ??? ???? ????? "???? ODBC" ?? ??? ???????) ???? ???? ??? (2) ??????? ??????? ????? ?????? Excel: ??? ?????? ??????? ???? ????? ???? ?????.

???: ????? ODBC ??? Excel ??????? ??? ???? ???????. ADO ????? ?? ????? ??????? LockType ?????? ??????? ?? ?????? ??? ??????? ????? ???????. ??? ???? ????? ReadOnly ??? ??? ?? ????? ??????? ?? ????? DSN ????? ?? ??? ???? ?????? ???????? ?????? ??. ????? ????? ????? ????? ???????:
??? ?? ?????? ????? ??????? ???? ???????.
???? ODBC ???????? ????? ????? Dsn-
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "MSDASQL"
	.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"
	.Open
End With
				
??????? ????? ????? ?? DSN ???? ODBC
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "MSDASQL"
	.ConnectionString = "DSN=MyExcelDSN;"
	.Open
End With
				
???? ODBC ???????? ???? ?????? ????? ?????? ????????

??? ??? ?????? ???? ???? ?????? ADO ?? ???? ???????? ?? ??????? ????? ??? ?? ???? ?????? ????? ?????? ???????? ?????? ???? ??????? ???????? ???????.
  1. ?? ????? ??????? ???? ? ??? ???? Microsoft OLE DB ?????? ????? ODBC.
  2. ??? ????? ??????? ????? ? ??? DSN ???????? ???? ???? ?????????? ?? ???? ??????? ????? ???????. ???? ????? ???? ?????? ????? DSN ???????? ????? ??????? ??????? ????????. ???? ?? ???? ?????? ????? ????????? ????? ??????? ??? ??? ??? ???????? ??? ??? ?????.
  3. ?????? ??? ????? ??????? ????? ? ????? ??? ?????? ???????. ???? ?? ???? ???? ????? ????? ??? ??????? ?????.
??????? ????? ???? ODBC ????

?????? ???????: ?????????? ??? ??????? ?? ???? ????? ?? ???? ???????? Excel ????? ??? ?????? ???????? ???? ???? ???????? ???? ?????. ??? ?? ??? ??? ??????? ??? ????? ??? ???????? ?? ???? ????? ?? ?? ???????? "?????" ?????????? ?????? ????. ??? ??? ?? ???? ????? ??????? FirstRowHasNames = ????? ??? ????? ???????. ?????????? ??? ??? ????? ??? ?????? ??? FirstRowHasNames = 1? ??? 1 = True. ??? ?? ??? ???? ?????? ???????? ??? ????? FirstRowHasNames = 0? ??? 0 = False; ?????? ??????? ????? ?????? F1? F2? ?????. ??? ?????? ??? ????? ?? ???? ?????? ????? DSN.

??? ???? ????? ??? ?? ?????? ????? ODBC? ????? FirstRowHasNames ????? ????? ?? ?? ?????. ????? ???? ODBC Excel ????? ?????? ??????? (MDAC 2.1 ?????????? ??????) ????? ???? ????? ?? ???????? ??????? ???? ?????? ????. ?????? ??? informationon ?????? ????? ?????? ?????? ???? ??? ??? ??????? ?????? ?????? ?? ????? ??????? ? Microsoft:
288343 BUG: ?????? ?????? ????? ODBC Excel ????? ??? ?? FirstRowHasNames
???? ?????: ?? ???? Excel ADO ?? ???????? ????????? ??????? ??? ???????? ???? ???????? ??? ?? ????? ?????? ???????. ????? ??? ?? ???? ?????? ??????? ?? ???? ??? ????? ??? ?????? ???????? ???????? ???? ????? ?? ??? ???????? ??? ????. ????? ?????????? ? "???? ??? ??????" ???? ?????? (8). ????? ????? ???? ??? ???? ?? ???? (1) ??? ??? ??? (16) ??????? ?? ????? ????? ??? (0) ????? ???? ?????? ????????. ??? ??? ?? ???? ????? ?????????? MaxScanRows = ????? ?????? ???????? ?? ?? ???? ????? ??????? ???? ????? ?? ???? ?????? ????? DSN.

??? ???? ????? ????? ??? ?? ?????? ????? ODBC? ????? ?????? ???? (MaxScanRows) ????? ????? ?? ?? ?????. ????? ???? ????? Excel ODBC (MDAC 2.1 ?????????? ??????) ????? ???? ?????? ???????? ?????? ?? ???? ???????? ?????? ?? ??????? ?????? ??? ?????? ?? ????.

????? ?? ????????? ??? ?????? ???? ?????? ??? ?? ??? ?? ?????? ???? ??? ??? ??????? ????? ?????? ?? "????? ??????? ? Microsoft":
189897 XL97: ?????? ??? 255 ????? ???????? ?????? ????? ODBC Excel ????????
??????? ????: ??? ??? ?????? ????? ??????? ?????? ?? ???????? ???? ?????? " ????? ?????? ???????? "? ?? ????? ??? ????? ????? ????????? ?????? ????? ??? ????? ??????? ??? ?????? ??? ??????? ????????? ???:
... DefaultDir=C:\WorkbookPath;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;
				
????? ????? "????? ????? ?????" ?? ???? Visual Basic

?? ???? ??????? Visual Basic ?? ??? ??????? MDAC? ?? ????? ??? ????? ?????? ??? ??????? ?????? ?????? ?? ???????? ???????? ????? ?????? Excel ?? ??? ???????:
????? ????? ??? ?????? ?? ??? ???? ??????? ??????.
??? ??????? ???? ??? ?? IDE ??? ???? ?? ??????? ????? ?? ????????. ?? ??? ?????? ??? ??????? ??????? ???? ??? ??? ??????? ?????? ?????? ?? ????? ??????? ? Microsoft:
246167 PRB: ?????? ??????? ADODB ????? ?? ????? ????? ????? ?????? ????? Excel XLS ?????

???????? ???? ??? ??????? ??? ??? ????? ????? ?????? OLE

????? ??? ????? ?????? ??????

??? ?? ????? ?????? ??? ?? ???? ADO ??? ???????? ??? ???? ?? ???? ??? Excel ?? ????. (??? ?? ????? ??????? ????? ?????? excel.) ???? ?? ???? ????? ????? ??? ??? ???? ????? ??????? ?????? ?? ????? ?????? ?? ??? ??????. ?? ?? Jet ? ????? ?????? ????? ???? ???? ODBC? ???? ????? NULL (?????) ???? ????? ???????? ???????. ??? ??? ??? ??? ???? ??????? ??????? ?? ??????? ????? ?????? ??????? ??? ????.

??? ???? ??????:
  • ?? ????? ?? ?????? (8) ???? ??????? ??? ??? ?????? ????? ??? ???? (5) ??? ????? ?????? (3) ????? ??????? ?????? ????? ??? (5) ??????? ?????? ??????? ????? (3).
  • ?? ????? ?? ?????? (8) ???? ??????? ??? ??? ?????? ????? ??? ????? (3) ????? ??????? ???? ???? ??? (5)? ?????? ????? ????? (3) ???? ????? ????? ?????? ?????? ??? (5).
  • ?? ????? ?? ?????? (8) ???? ??????? ??? ??? ?????? ????? ??? ????? (4) ??? ????? ?????? (4) ????? ??????? ?????? ?????? ????? (4) ??????? ?????? ??????? ????? (4).
?????? ????? ??? ??? ?????? ????? ??? ??? ??????? ????? ?? ??? ??? ?????? ?????? ????? ??????? ?? ??? ?????? ???? ???????? ?????? ??? ????? ??? ?????? ???? ?? ????? ?????? ???????? Visual Basic ?????? VAL ?? ?????.

?????? ??? ??? ??????? ?????? ??????? ???? ?????? ??? ????????? ???????? ??????? "????? = 1" ?? ?????? "????? ?????" ????? ???????. ??? ???? ImportMixedTypes = Text ????? ???????. ??? ???? ???? ?? ????????? ?? ???? ??? ????? ??????? ?? ??? ?????. ?????? ??? ??????? ?????? ??? ??? ???????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
194124 PRB: ????? NULL ???????? DAO OpenRecordset ??? Excel
?? ????? ??? ???? ???? ????? ????

??? ??? ???? Excel ?????? ????? ????? ????? ?? ???? ???? ?? ??? ?????? ??? ????????? ??? ?????? ???? ?????? ??????? ?? ??????? ??????? ?????? ??? ??? ??? ??? ??? ?????? ?????? ??? ?? ????? Microsoft Excel. ??? ??? ?????? ????? ????? ?????? ???????:
?? ???? ?? ????? ?????.
????? ?? ?????????? ???? ??? ??? ??????? ?????? ?????? ?? ???? ????? Microsoft ?????:
211378 XL2000: ??? "???? ?? ????? ???????" ???????? ???? ???? ????? ??????

??????? ?????? ?????? Excel ???????? ADO

????? ??? ????? ?????? ????? ?? Excel ????? ?? ??????:
  • ??? ??? ????? ????????-?-

  • ????? ????? ????????

??? ??? ????? ????????

???? ??? ??? ?????? ????????. ?????:

  • ??? ?????? Excel ?? ????????? ????????.
  • ??? ?????? Excel ?? ???? ???? ?????? ADO.
  • ??? ?????? Excel ?? "???? ????????" ?????.

??? ?????? Excel ?? ????????? ????????

?? ????? ?????? Excel ?? ?????? ????? ?? ?? ??? ???:

  • ???? ??? ?????.
  • ???? ???? ?? ??????? ??? ???? ???.
  • ???? ??? ???? ?? ??????? ?? ???? ???.
????? ???? ???

?????? ???? ????? ????? ????? ????? ??? ?????? ??? ???? ????? ??????? ?????? ??????? ?????? ?????? ?????. ?? ??? ??? ???? ??????:
	strQuery = "SELECT * FROM [Sheet1$]"
				
????? ???? ????? ??? ???? ????? ???????? ????? ?????? ????? ????? ????? (') ??????? ??? ???? ???????? ??? ?????? (~). ??? ???? ??????:
	strQuery = "SELECT * FROM `Sheet1$`"
				
???? Microsoft ??????? ???????? ??? ????? ?????? ?????? ?????? ????? ???????? ????? ???.

??? ???? ?????? ????? ??????? ????????? ?? ????? ???????? ???? ????? ????? ???????:
... ???? ????? ?????? Jet ???? ?????? ??? ?????? ??????
??? ??? ?????? ????? ??????? ??? ??? ???????? ??? ??? ????? ????? ???????:
??? ?? ???? ?????? ?? ???? FROM.
??? ??? ????? ?????? ?????? ???????? ??????? ???????? ????? ????? ????? ???????:
??? ?? ???? ?????? ?? ?????????. ????? ????????? ??? ?????.
????? ???? ????

?????? ???? ???? ?? ??????? ????? ?????? ?????? ??????? ??? ????. ??? ???? ??????:
	strQuery = "SELECT * FROM MyRange"
				
????? ???? ??? ????

?????? ???? ??? ???? ?? ??????? ????? ?? ???? _ ?????? ????? ???? ????/?????? Excel ???????? ?????? ?????? ????? ?????? ?????. ??? ???? ??????:
	strQuery = "SELECT * FROM [Sheet1$A1:B10]"
				
????? ??? ????? ????? ?????: ?????? ????? ?? ???? ???? ???????? ????? ??????? ???? ??????? ??? ????? ?? ???? ????? ???????. ?? ????? ???? ???? ?? ???? ???? ???????? ?? ???? 3? ?????? C ??? ???? ??? ??????. ??? ???? ?? ?????? ??? ???? ??????? ????? ????? ???????? ???? ???? ?????? ???????? ???????? ?? ?????? A1.

????? ??? ????? ??????: ??? ????? ???? ????? ????? ?????? ?????? ????? ????? ????? ????? ??????? ???????? ?? ???? ????? ?????? ????. ??? ????? ???? ???? ?? ??? ???????? Jet ???? ?????? ????? ????? ???? ??????? ???????? ?? ?????? ?????? ????. ??? ???? ??? ??? ?? ????? requery ??? ?????? ?????? ??????? ??????? ??????? ??????? ????? ???? ??????.

?? ??????? MDAC ??? 2.5? ????? ???? ?????? ???? ????? ?? ????? ????? ????? ????? ??? ????? ???? ?????? ?? ????? ????? ????? ???????:
?? ???? ????? ?????? ??????.

??? ?????? Excel ?? ???? ???? ?????? ADO

??? ????? ??????? ??????? ??????? Excel ?????? ?? ???? ?? ????? ??????? ??? ?? ???? ?????? ADODC ????? ? ???? ??? ????? ??????? "???? ?????" . ??? ??? ??????? ?????????? ??????????? ????? ????? ??? ????????? ?? ???? ?????? ?? ????? ?? ???? ?????? ?? ???? ??????. ??? ????? ?????????? ?????????? ???? ?????? ???? Jet? ??????? ?????? ???? ????? ???????? ??????? ?????? ????? ????? ???????? ?? ?????? ??????? ?? ?????? ????? ??? ?????.

???? ?????? ??? ???? ???? ???? ?????? ????? ??????? ?????? ????? ?????? ???? ?? ???? ?????? ????? ????? ????????. ?????? ????? ??? ??? ?????? ?????? ????? ????? ????? ???? ??? "?????"? ????? ????? ????? ??????? ??? ???:
??? ?? ???? ?????? ?? ???? FROM.
??? ???? ????? ????? ????? ??? ??? ???? ????? ??????. (??? ???? ??????? ?????? ?????? ????????.) ??? ??? ?????? ???? ODBC? ???? ??? ??? ???????? ???????? ?? ??? ??????? ????????. ??? ???? ????? ????? ?????? ??? ???? ????? ???????? ???????? ????????.

??? ?????? Excel ?? ????? ???? ????????

??? ????? "????? ???? ????????" ? Excel ????? ?? ???????? ??????? ?? ?????? ???? ??? ????. ??? ??? ??????? ???? ????????????? SQL? ????? ????? ??????? ?? ???? ???? ???????? ???? ?????? ?? ???? ??????. ??? ??? ??????? ???? ???????????? ????? ????????? ??? ?????? ?? ??????? ???????? ??????? ???? ?????? ???? Jet? ???? ??????? ???????? ???????? ??????? ?????? ????? ????? ???????? ?? ?? ?? ?????? ??????? ?? ?????? ????? ??? ?????. (??? ??? ??????? ??? ???? ????? ?? ??? ??????? ?? ????? ?????? ????? ????? ????? ??? ???? ????? ?????? ??? ??? ???? ????? ???? ?????? ADO.) ??? ??? ??????? ???? ODBC? ???? ???????? ??????? ???????? ?? ??? ??????? ???????? ?????. ??? ???? ????? ????? ??? ???? ??? ??????.

????? ????? ?????? Excel: ????? ?????? ????

?????

????? ????? ?????? Excel ?? ?????? ADO ???????. ???? ?????? ??????? ???? ?????? ?? ??????? ?? ???? ??? Excel ????? ??? ??? Excel (???? ? "=") ?? ??????? ??? ??? ???? ??????. ???? ?? ????? ODBC ??? Excel ??????? ??? ???? ???????? ??? ??? ??? ????? ??? ??? ?? ??????? ??????? ?????? ??. ???? ????? ??? "??????? Microsoft OLE DB ???? ?????? ????? ODBC. "

?????

????? ????? ??????? ??? ???? _ ????? Excel ????? ?? ?????? ????. ??? ???? ??? ??? ?????? ???? ??????? ???? ?????? ??? ????? ????? ??? ??????? ??? ????? ??? ??? ????????? ??? ?????? ?????? ???????. ???? ????? ??? "????? ??? ????? ??????."

?? ??? ???????? ??? ??????? ?????? AddNew ? ????? ???? ADO ?????? ??????? ?????? ???? ????? ?? ???????? ?? ???? Excel ADO ?? ???? ??? ???????? ?? ??????? ??? ???? ?? Excel. ????? ?? ?????????? ???? ??? ??????? ??? ????? ?????? ?? "????? ??????? ? Microsoft":
314763 ?????: ADO ????? ???????? ?? ??????? ??? ???? ?? Excel
???

???? ????? ?? ??? ?????? Excel ?? ?????? ?? ???? ?????? ???????. ?? ????? ?????? ???????? ??? ?? "????" ???? ?? ???? ????? ????? ?? "?????"; ?? ???? ??? Excel? ?? ????? ??? "true". ????? ??? ????? ?? ?????? (???????). ??? ???? ?? ?????:
  1. ??? ??? ?????? ??? ????? ?? ???? ????? ????? ???????:
    ??? ???????? ?? ???? ????? ??? ????? ?? ??? ISAM ???.
    ????? ??? ??? ??? ??? ????? ?? ??????? ?? ??? ????.
  2. ??? ?????? ?? ???? ????? ??? ???? Excel ?? ????? ????? ????? ???????:
    ??????? ????? ?? ?? ??? ??????.
  3. ?? ????? ??? ???? (??????) ???? ?????? ???? ??? ?? ??? ???????? ????????? ????? ?????? ?????? ??????? ?????? ?? ???? ???? ??????? ???????? ???? ?????? ?????.
????? ??? ????? ?????? Excel ???????? ADO: ??? ????? ?????? ???? ?? Excel ???????? ADO? ???? ???? ??????? ?????? ?????? ?????. ??? ?? ???? ????? ?? ??? ???? ????? ?? ???????? ???????.

??????? ???? ???? ???????? (?????? ???????) ?? Excel

????? ??????? ???????? ??? ???? ?????? Excel ?????? ?? ???? (??????? ???????) ???????? ADO. ??????? ????? ?????? ??? ??????? ????? OLE DB? ??? ????? ?? ????? ??? ??? ???? ?? ????? ??? ????? ???? ?????????. ???? ??????? ?????? ??????? ??? ???????? ??????? OpenSchema ???? ADO ????? ? ????? ???? ?????? ???? ADO ?????? ??????? . ????? ???? ??????? ?????? ActiveX Microsoft ???? ??? ?????? ?????? ?????? "??? ????? ????????" ? Security (ADOX) ??? ?????. ?? ???? ???? ?????? Excel ??? ???? ??? "????" ???? ??? ?? ???? ???? ? "???" ????? ?? ??? ????? ?? ????? ???????? ??????? ??? ???? ?????? ??? ?????.

????????? ?? ??????? ??????

?????? ?????? ?????? ?? ????? ?????? ??????? (??????? ???? ?????? ?????????? ??????? ??? ??? ???)? ???? ?? ???? ?????? Excel ??? ?????? ?????????? ????? ?? ????? ????? ????????? ??????? ??????? ?? ?????? ??????. ????? ??? ???? "?????" ???????? ??????? ? ????? ????? ????? ??? ???? "????? ??????"? ???? ???? ?????? ?????? ????? ??????? ????? ??????? ???? ??? ??????? "table_type". ????? ??? ????? ??????? ???????? ?? ?????? ?? ????????? ???????? ???????:
Set rs = cn.OpenSchema(adSchemaTables)
				
???? Jet ?????? ?????? ????? ????? ???? (9)? ???? ???? ???? ??? ????? (4):

  • table_name
  • table_type ("????" ?? "????" ?????? ")
  • date_created
  • date_modified
??????? ??? ?????? ????? ???? ?????? ????? ??? ?????? ???? ???? ?????? "????? ??????? ??????." ??????? ????? "date_created" ??? ????? ???.

????? ???? ODBC ???? ?????? ??????? ????? (9) ????? ???? ??? ??? ??? ????? (3):

  • table_catalog? ?????? ??? ?????? ??????.
  • table_name.
  • table_type? ??? ?? ???? ?????.
???? ?????? ADO? ?? ?????? ??????? ????? ?? ????? ????? ???? ??? ???? ??????? ?? ???? ????? ??????? ?????? ?????? ??????? OpenSchema :
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "System Table"))
				
?????? ?? ???? ??? ??????? ????? ???? ?????? Excel ?? ??????? MDAC ???? ?? ??????? 2.0? ??????? ?? ????.

????????? ?? ??????? ?????

????? ?? ?? ??? (????) ?? ???? ?????? Excel ????? ???????? ???????:

  • ???? (??? ???????? ADO 5? ??????)
  • ???? (??? ???????? ADO 6? ?????????)
  • ????? ?? ???? ?????? (??? ???????? ADO 11? ????????)
  • ??????? (??? ???????? ADO 7? ?????? ?????? Jet; 135? ????????????? ??????? ODBC)
  • ?? (????? ADO...???? ??? 202? ????? ?????????? 200? ?????????? ?? ?? ????)
?????? ??? ????? numeric_precision ????? ???? ? 15 (??? ???? ?????? ????? ?? Excel). character_maximum_length ?? ?????? ??? ????? ?????? ? 255 (??? ??? ???? ?????? ?????? ???? ??? ???? ?????? ???? ?? ?? ???? Excel). ??? ???? ?????? ??????? ????? ????? ???? ????? ?????? ??? ???? ??????? data_type . ??? ????? ?????? ???????? ?? ???? ?? ????????? ???????? ???????:
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, "TableName", Empty))
				
???? Jet ?????? ????? ???? ????? ??? ???? 28 ???? ???? ???? ???? ?????? (8) ?????? ??????? ????? (9) ????? ????. ????? ?? ??????? ?? ??? ?????? ?????:

  • table_name
  • column_name
  • ordinal_position
  • data_type
???? ODBC ?????? ?????? ????? ????? ??? ???? 29? ?? ??? ??? ???? ???? ???? (10) ?????? ??????? ? 11 ????? ????. ????? ???? ????? ?? ????? ??? ?? ??? ????.

????? ??????? ??????? ????????

???? ??????? ????????? ???????? Visual Basic (??? ??????? ??????) ????? ??????? ???????? ?? ???? ?????? Excel ????????? ???? ??????? ??? ?? ????. ??? ?????? ????? ??????? ?????? ?? ?? ???? ?????? List1 ?? ??? ???????.
Dim cn As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connection
With cn
	.Provider = "Microsoft.Jet.OLEDB.4.0"
	.ConnectionString = "Data Source=" & App.Path & _
"\ExcelSrc.xls;Extended Properties=Excel 8.0;"
	'.Provider = "MSDASQL"
	'.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & App.Path & "\ExcelSrc.xls; "
	.CursorLocation = adUseClient
	.Open
End With
Set rsT = cn.OpenSchema(adSchemaTables)
intTblCnt = rsT.RecordCount
intTblFlds = rsT.Fields.Count
List1.AddItem "Tables:	" & intTblCnt
List1.AddItem "--------------------"
For t = 1 To intTblCnt
	strTbl = rsT.Fields("TABLE_NAME").Value
	List1.AddItem vbTab & "Table #" & t & ":	" & strTbl
	List1.AddItem vbTab & "--------------------"
	For f = 0 To intTblFlds - 1
		List1.AddItem vbTab & rsT.Fields(f).Name & _
vbTab & rsT.Fields(f).Value
	Next
	List1.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
		List1.AddItem vbTab & vbTab & "Column #" & c & ": " & strCol
		List1.AddItem vbTab & vbTab & "--------------------"
		For f = 0 To intColFlds - 1
			List1.AddItem vbTab & vbTab & rsC.Fields(f).Name & _
vbTab & rsC.Fields(f).Value
		Next
		List1.AddItem vbTab & vbTab & "--------------------"
		rsC.MoveNext
		Next
		rsC.Close
		List1.AddItem "--------------------"
		rsT.MoveNext
Next
rsT.Close
cn.Close
				

??????? ???? ??? ??????

??? ??? ?????? ?????? ?????? ??? ???? ?????? Excel ?? ??????? ???? "????? ??? ????????" ????????? ???? ?????? "??? ????????" ??? ????????? ???? ????? ??????? ??????? ??? ?? ???? ?????. ????? ???? ???? ?? ???? Jet ???? ?? ?? ????? ????? ????????? ??????? ??? "?????" ??? ???? ???? ODBC ??? ?????? ?????. ??? ??? ?????? ODBC ???? ??? ???? ?? ???????? ???????? ????? ????? "?????" ????.

???? Excel

??????? Excel ????? ?????? ????? ?????? ???? ?????? Excel ?????? ?????. ??? ?????? ????? ?? ???? ?:

  • ??? ???? ?????: 65536 ???? ? 256 ??????
  • ?????? ????????? (????): 32767 ?????
  • ??????? ?? ????: ???? ???????? ???????
  • ??????? ?? ????: ???? ???????? ????????

?????

????? ?? ????????? ??? ????? ??????? ADO.NET ??????? ?????? ??????? ?? ???? Excel ???????? Visual Basic.NET? ???? ??? ??? ??????? ?????? ?????? ?? ???? ????? Microsoft ?????:
316934????? ??????? ADO.NET ???????? ?????? ??????? ?? ???? Excel ???????? Visual Basic.NET
????? ?? ?????????? ???? ??? ????? ???????? ??????? ?????? ?? "????? ??????? ? Microsoft":
295646 ????? ??? ???????? ?? ???? ?????? ADO ??? Excel ???????? ADO
246335 ????? ??? ???????? ?? ?????? ????? ADO ??? Excel ???????? ??????? ????????
247412 INFO: ?????? ???? ???????? ??? Excel ?? Visual Basic
278973 ?????: ????????? ???? ????? ??????? ADO ?????? ?????? ???????? ?? ?????? Excel
318373 ????? ??????? ?????? ??????? ?? Excel ???????? ??????? ???????????????? ?? Visual Basic.NET

???????

???? ???????: 257819 - ????? ??? ??????: 17/????? ??????/1434 - ??????: 6.0
????? ???
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic for Applications 6.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
????? ??????: 
kbhowto kbiisam kbmt KB257819 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????257819

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com