???? ID: 257819 - ????? ???????: 17 ??????? 2011 - ??????: 3.0

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

???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
?????? ??????This article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

?? ????? ??

??? ?? ??????? ???? | ??? ?? ??????? ????

??????

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

???????? ??????? ?? ???, ???? ?? ???? ?????? ?? ????? ?? ???? ?? Microsoft ???????? ??? ?????::
303814  (http://support.microsoft.com/kb/303814/EN-US/ ) Visual Basic ?? VBA ?? ???? ?? Excel ?? ??? ADOX ?? ????? ???? ????
????? ?? ???? ????

???? ???????

?????

????????? ?? ??????? ?? ???? Microsoft Excel ?????????? ?? closely resemble ????????? ?? ??????? ?? ?? ??????? ?????? ??? ?? ?? ?????????? ????? ?? Microsoft Excel ???? ?? ??? ??????? ??????? ??????? ?????? ??? ????, ?? ?? ???? imposes ?????? ?????, ????? sense ?????? ?? ???? ?? ???????? ???? ?? ??? Excel ?? ???? ??????? ?? ??? ???? ?? ??? ???? ???? ???

Microsoft ActiveX ???? ???????? ??? ????? ?? ??? Excel ????????????? ?? ??? ???? ??????? ?? ?? ?? ??? ??? ????? ???? ??? ?? ???? ????? ???? ?? ?? ????? ???????? ??? ???? ???? ?? ??? ???? ????:???:: ?? ???? ?? ??? ??????? ?? ??? Microsoft ???? ?????? ????? (MDAC) 2.5 Visual Basic 6.0 ?????? ??? 3 ?? ??? Microsoft Windows 2000 ?? Excel 2000 ?? conducted ???? ??? ??? ?? ???? ?? ???? ?? ???? acknowledge ?? ?????????? ???? MDAC, Microsoft Windows, Visual Basic, ?? Excel ?? ????? ??????? ?? ??? ?? ???? ?? ?? ??????? ??? ???? ?? ????? ?????
????? ?? ???? ????

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

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

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

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

??? ??????? ?? ???? Excel ???? ????? ?? ?????? ???? ?? ??? ??????? ?? ???? ?? ?????? ?? ????????: ??, ???? ??? ?? Excel ????? ??????? ????? ????

??? ??????? ?? ??????? ???????? ?? ????? ????
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 ??????? ?? ????? ???? ?? ??? ?????? ??; ??? 3.51 ??????? ??? ISAM ??????? ?? ?????? ???? ???? ??? ??? ?? ????????? ??? 3.51 ???????, ?? ????? ??? ???? ????? ?????? ????? ??????? ???? ??:
??????? ????? ISAM ??????? ???? ?? ????
Excel ???????: Excel 5.0 ?? ???? Excel 95 ????????????? ?? ??? ????????? (??????? 7.0 ?? Excel ??), ?? ???? Excel 97, Excel 2000 ?? Excel 2002 (XP) ????????????? (??????? 8.0, 9.0 ?? Excel ?? 10.0) ?? ??? Excel 8.0?

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

??? ?? ADO ???? ???????? ?? ???? ?? ??????? ??? ?? ????? ???? ????????? ??????? ???? ????????? ??????? ???????? ??????? ???? ?? ??? ????? ????? ?? ????????? ???? ???? ???
  1. ????? ????Provider??? ??, Jet 4.0 ??????? ?? ??? ????; ??? 3.51 ??????? ??? ISAM ??????? ?? ?????? ???? ????? ??? ?? ??? 3.51 ??????? ????????? ??, ????? ??? ??? ???? ????? ?????? ?????:
    ??????? ????? ISAM ??????? ???? ?? ????
  2. ????? ?????????????? ???, ???? ????????????? ???? ?? ??? ?????? ????? ??????? ?? ???? Excel ??????? ?? ???? ???? "?????????? ID" ?? "???????" ???????????? ??, ?? ????? ? ???? (?? ???? ??? ???????-???????? Excel ????? ???? ???? ????? ?? ??? ???? ?? ?? ???? ??? ??? ??? ?? ???? ?? ???? ?????.)
  3. ????? ?????????? ?? ??? ????????????? ???????, ?? ???? ?????? ?? ??????? ????. ENTERExcel 8.0;???? ?? ???? ?????? ???????????? ????????? ?? ?? ????? ???? (;)? ??? ?? ?? ??? ?? ?? ??? ??, ?? ???? ?? ?????? ????? ?? ?? ???? ??????? ?? ??????? ??????? ??? ??????? expects ???? Microsoft Access ??????? ?? ?? ?????? ????????? ?????
  4. ????? ?? ??????????????? ???? ?? ?????????? ???????. Note that a message box appears informing you that the process has succeeded.
Other Jet Provider Connection Settings

Column headings: By default, it is assumed that the first row of your Excel data source contains columns headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optionalHDR=setting to theExtended Propertiesof the connection string. The default, which does not need to be specified, isHDR=Yes. If you do not have column headings, you need to specifyHDR=No; the provider names your fields F1, F2, etc. Because theExtended Propertiesstring now contains multiple values, it must be enclosed in double quotes itself, plus an additional pair of double quotes to tell Visual Basic to treat the first set of quotes as literal values, as in the following example (where extra spaces have been added for visual clarity).
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=" " Excel 8.0; HDR=No;" " "

Using Microsoft OLE DB Provider for ODBC Drivers

The provider for ODBC drivers (which this article refers to as the "ODBC Provider" for the sake of brevity) also requires only two (2) pieces of information in order to connect to an Excel data source: the driver name, and the workbook path and filename.

??????????: An ODBC connection to Excel is read-only by default. Your ADO RecordsetLockTypeproperty setting does not override this connection-level setting. You must setReadOnly???? ?? ??????in your connection string or your DSN configuration if you want to edit your data. Otherwise, you receive the following error message:
Operation must use an updateable query.
ODBC Provider Using a DSN-Less Connection String
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
ODBC Provider Using a Connection String with a DSN
Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
	.Provider = "MSDASQL"
	.ConnectionString = "DSN=MyExcelDSN;"
	.Open
End With
ODBC Provider Using the Data Link Properties Dialog Box

If you use the ADO Data Control or the Data Environment in your application, then theData Link Propertiesdialog box is displayed to gather the necessary connection settings.
  1. ????? ????Providertab, selectMicrosoft OLE DB Provider for ODBC Drivers.
  2. ????? ????Connectiontab, select the existing DSN that you want to use, or chooseUse connection string. This brings up the standard DSN configuration dialog box to gather the necessary connection settings. Remember to deselect the default read-only setting if desired, as mentioned previously.
  3. Return to theConnectiontab, and clickTest Connection. Note that a message box appears informing you that the process has succeeded.
Other ODBC Provider Connection Settings

Column headings: By default, it is assumed that the first row of your Excel data source contains columns headings, which can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optionalFirstRowHasNames=setting to the connection string. The default, which does not need to be specified, isFirstRowHasNames=1, ????1 = True. If you do not have column headings, you need to specifyFirstRowHasNames=0, ????0 = False; the driver names your fields F1, F2, and so forth. This option is not available in the DSN configuration dialog box.

However, due to a bug in the ODBC driver, specifying theFirstRowHasNamessetting currently has no effect. In other words, the Excel ODBC driver (MDAC 2.1 and later) always treats the first row in the specified data source as field names.For additional informationon the Column Heading bug, click the article number below to view the article in the Microsoft Knowledge Base:
288343  (http://support.microsoft.com/kb/288343/EN-US/ ) BUG: Excel ODBC Driver Disregards the FirstRowHasNames or Header Setting
Rows to Scan: Excel does not provide ADO with detailed schema information about the data it contains, as a relational database would. Therefore, the driver must scan through at least a few rows of the existing data in order to make an educated guess at the data type of each column. The default for "Rows to Scan" is eight (8) rows. You can specify an integer value from one (1) to sixteen (16) rows, or you can specify zero (0) to scan all existing rows. This is done by adding the optionalMaxScanRows=setting to the connection string, or by changing theRows to Scansetting in the DSN configuration dialog box.

???????, ODBC ??????? ??? ??? ?? ?? ???? (MaxScanRows) ?? ????? ???? ?? ??? ????????? ????????? ???? ??? ??? ??? ?????? ???? ??? ????? ?????? ???, Excel ODBC ??????? (MDAC 2.1 ?? ??? ???) ????? ????? ???? 8 ????????? ????????? ???? ????? ??? ???????? ????? datatype ????????? ???? ?? ??? ?????

????? ??, ??? ??????? ?????? ????? ???? ?? ??? ????????? ?? ???? ??? ???????? ??????? ?? ??? Microsoft ???????? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
189897  (http://support.microsoft.com/kb/189897/EN-US/ ) XL97: ???? ?? Excel ODBC ??????? ?? ??? 255 ???? ????
???? ????????: ??? ???? ??????? ???????? ?? ????? ???? ????????? ???? ???????? ????? ???, ?? ???? ????? ??? ????????????? ??????????? ????? ??? ??????? ???????? ?? ??? ?????? ?????? ??, ???? ???? ???:
... DefaultDir=C:\WorkbookPath;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;
"??????? collating" ?????? ????? Visual Basic Editor ???

Visual Basic ??????? ??????? ??? MDAC ?? ??? ????????? ?? ???, ?? ????? ??????? ??? ?????? ???? Excel ???? ????? ?? ???? ????????? ?????? ????? ???? ??? ??? ???? ???:
????? collating ??????? ???????? ?????? ?? ?????? ??????? ???? ???
?? ????? ?? ???? IDE ??? ????? ???? ?? ?? ????????? ?????? ??????? ??? ????? ???? ????????????? ??????? ?? ???, ???? ?? ???? ?????? ?? ????? ?? ???? ?? Microsoft ???????? ??? ?????::
246167  (http://support.microsoft.com/kb/246167/EN-US/ ) PRB: Collating ??????? ?????? ????????? ADODB Recordset ????? ??? ?? ???? Excel XLS ?? ???????

????? ?? ???? ???? ?? ??? ????? OLE DB ???????

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

???? ????????, ?? ??? ??? ADO Excel ?????????? ?? ?????? ??? ???????? ????? ?? ??? ???? ?????? ?? ?????? ????? ???? ????? (?? ?? ???? ?? ???????? ???????? ??????? Excel ????.) ?? ????? ?????? arise ?? ???? ??? ??? ???? ??? ?? ?? ????? ??? ??? ?? ??? ?? ??? ??????? ?????????? ??? ??? ??? ?? ODBC ??????? majority ?? ???? ?????, ????? minority ???? ?????? ?? ??? ?? (????) ??? ?? ???? ???? ???? ???? ?? ?????? ?? ????? ??? ??????? ?????? ?? ???, ??? ??????? ????? ???????? ??? ?? ???

?????? ?? ???::
  • ??? ???? ?? (8) ????? ??? ??? ?????????, ????? ??? ???? (5) ???????? ??? ?? ??? (3) ??? ???, ??? ??????? ???? ???? (5) ???????? ?? ??? (3) ?? ????
  • ??? ???? ?? (8) ????? ??? ??? ?????????, ????? ??? ??? (3) ???????? ??? ?? ???? (5) ??? ???, ??? ??????? ???? ??? (3) ?? ??? ?? ???? (5) ??? ????
  • ??? ???? ?? (8) ????? ??? ??? ?????????, ????? ??? ??? (4) ???????? ??? ?? ????? (4) ??? ???, ??? ??????? ???? ??? (4) ???????? ?? ????? (4) ?? ????
????????????, ??? ???? ????? ??? ??? ?? ????-???? ??, ???? ???? recourse ?? ???????? ??? ?? ??? ?? ??? ??? ?? ????? ??? ?????? ???? ?? ???, ?? ?? Visual Basic ?? ????? ???? ?????? ??????? ????????? ??? ???????? ?? ?????? ???? ?? ???VAL?????? ?? ?? ???? ???

????-????? ?? ??? ???? ?? ??? ?? ?????? ?? ?? ???? ?? ??? ????? ???????? ????? ?????? ?? ????? ???? ??? "IMEX = 1" ??? ??????? ??? ?????? ?? ??????? ????????? ?? enforcesImportMixedTypes ??? =????????? ??????? ???????, ????? ??? ?? ?????? ?? ???? ?? ??? ????????? ?????? ?? ??? ??? ????? ?????? ?? ???? ??? ???????? ??????? ?? ??? Microsoft ???????? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
194124  (http://support.microsoft.com/kb/194124/EN-US/ ) PRB: Excel ??? NULL DAO OpenRecordset ????? ?? ??? ??? ??? ??
???????-?????? ????????????? ????? ????

??? Excel ????????????? ?? ??????? ?????? ???????? ??, ?? ?? ??? ???? ???? ?? ???? ????? ?? ???, ???? ??? ??????? ???? ??????? ???????? ?? ??? ????????????? ???? ???? ?? ?? Microsoft Excel ????????? ??? ????? ?? ?? ?? ?? ?????? ??? ??? ???? ?? ?????? ??, ?? ???? ????? ?????? ????? ????? ??:
???? ?? ????????? ???? ?? ????
???????? ??????? ?? ???, ???? ?? ???? ?????? ?? ????? ?? ???? ?? Microsoft ???????? ??? ?????::
211378  (http://support.microsoft.com/kb/211378/EN-US/ ) XL2000: "??? ???? ????????? ?????" ?????? ??????? ?? ???????? ?????
????? ?? ???? ????

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

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

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

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

???? ?? ??? ???? ?? ??? ?? ????? ???? ?? ?? ???? ???:

  • ??? ?? ??? ???? ?? Excel ?? ??? ?????
  • ADO ???? ???????? ?? ??? ???? ?? Excel ?? ??? ?????
  • ???? ?? ??????? ?? ??? ???? ?? Excel ?? ??? ???? ?????

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

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

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

???? recordsource ?? ??? ??? ??? ?????????? ????????? ????, ?? ???? ??? ?? ???? ????? ?? ???? ?????? ?????? surrounded ?????????? ??? ?? ????? ????? ?????? ?? ???::
	strQuery = "SELECT * FROM [Sheet1$]"
?? ?????????? ??? slanted ??? ?????? ???? (') tilde (~) ?? ??? ???????? ?? ?? ??? delimit ?? ?? ???? ???? ?????? ?? ???::
	strQuery = "SELECT * FROM `Sheet1$`"
Microsoft ?? prefers ???? ??????, ?? ?????? ???? ???? ???? ??????? ???????? ????? ?? ??? standing ????? ????

??? ?? ?? ?? ????? ????? ???? ????? ?? ??????, ?? ???? ???? ?????, ?? ???? ????? ?????? ????? ????? ??:
... Jet ??????? ????? ????????? ???????? ???? ??? ???
??? ?? ???? ????? ?? ????? ????? ?????? ?? ?? ????? ??, ?? ???? ????? ?????? ????? ????? ????:
???????? ?????? ??? ?? ????
??? ?? ??????? ??? ???????? ?? ????? ???? ?? ?????? ????, ???? ????? ?????? ????? ????? ??:
?????? ??? ???????? ??????? ?????? ?????? ?? ????
?? ??? ?????? ????????? ????

???? recordsource ?? ??? ??? ???????? ?????? ?? ?????? ????????? ????, ???? ?? ??? ????? ????????? ??? ?? ????? ????? ?????? ?? ???::
	strQuery = "SELECT * FROM MyRange"
??? ???? ?????? ????????? ????

???? recordsource ?? ??? ??? ?? ???? ?????? ?? ?????? ????????? ????, ???? ?? ??? ???? Excel ??????/????? ?????? ???? ?????? ??? ????? ?? ??? ?? ??? ??? ?????? ?????? ?? ???::
	strQuery = "SELECT * FROM [Sheet1$A1:B10]"
?????????? ?? ????????? ???? ?? ???? ??? ??? ???????: ??????? ????? ?? ?? ???? ???? ?????? ?? ???? ???? ????-???????, ????-???????, ????????? ?????????? ?? ?????? ???? ??? ????? ?????? ???, ???? ???? ?????? ?????? 3, ???? ???? ?????? ????? C ??? ???? ?? ???? ???? ???????, ?? ???? ?? ???? ?? ?????? ?? ???, ??? worksheeet ?????? ????? ??? ?? ???? ?? ???? ?? ??? A1 ????

????????? ?? ????????? ???? ?? ???? ??? ??? ???????: ?? ?? ???? recordsource ?? ??? ??? ??? ?????????? ????????? ????, ??????? ?????? ?????? ???? ?? ?????? ??? ???? ?????????? ?? ??? ??? ????? ???? ??? ?? ?? ???? ?????? ????????? ???? (??? ?? unnamed), ??? ?? ?????? ?????? ???? ?? ?????? ??? ???? ?????? ?? ??? ??? ????? ???? ??? ???????, ??? ?? ??? ?????? ?? requery, ??? ???? recordset ????? ???? ???? ?? ?????? ?????? ?? ?????

2.5, ???? ?? ???? MDAC ????????? ?? ??? ???? ?? ??? ?????? ????????? ????, ?? ?? ?????? ????????? ???? ?? ???? ?? ?????? ??? ???? ?? ????, ?? ???? ????? ?????? ????? ??????? ???? ??:
???????? ?????? ??????? ???? ?? ???? ???

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

After you specify the connection settings for your Excel data source on the???????tab of the ADODC???dialog box, click on theRecordsource??? ?? ????? ????.. If you choose a CommandType of adCmdText, you can enter a SELECT query in theCommand Textdialog box with the syntax described previously. If you choose a CommandType of adCmdTable, and you are using the Jet Provider, the drop-down list displays both the named ranges and worksheet names that are available in the selected workbook, with named ranges listed first.

This dialog box properly appends the dollar sign to worksheet names, but does not add the necessary square brackets. As a result, if you simply select a worksheet name and clickOK, you receive the following error message later:
Syntax error in FROM clause.
You must manually add the square brackets around the worksheet name. (This combo box does allow editing.) If you are using the ODBC Provider, you see only named ranges listed in this drop-down list. However, you can manually enter a worksheet name with the appropriate delimiters.

Select Excel Data with Data Environment Commands

After setting up the Data Environment Connection for your Excel data source, create a newCommand???????? ??? If you choose aSource of DataofSQL Statement, ???? ???? ???? ???????? ?? ????? ?? ??? ????? ??? ??? ?????? ???? ?? ???? ???? ??? ?? ????? ??? ?????? ?????????????? ??????????? ??????????????? ??? ?????-???? ???? ???, ?? ?? ????? ??? ?? ??? ??? ???????, ?????-???? ???? ????????? ???????? ????????? ?? ?????????? ??? ????? ????????????? ??? ?????? ???????? ????????? ???????? ???? ?? ??? (??? ?? ?? ????? ??? ??? ?????????? ??? ????? ???, ?? ???? ???? ????? ?? ??? ?????????? ??? ?? ??-??? ???? ?????? ???????? ??? ?? ?? ??? ??? ?? ADO ???? ???????? ?? ??? ???? ???.) ??? ?? ODBC ??????? ?? ????? ?? ??? ???, ?? ?? ???????? ????????? ???? ?? ?????-???? ???? ??? ???????? ????? ???? ???????, ?? ?? ???? ??? ???????? ??? ?? ???? ?????????? ??? ???? ?????

???? ?? Excel ?? ????????? ???? ?? ??? ????: ??????, ?????? ?? ?????

??????

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

add

????? ?? ?????? ???? ?? ???? ???? Excel recordsource ???? ?? ??? ?????? ?? ???? ????? ???????, ??? ?? ??? ??? ?? ????????? ?????? ?? ???? ?? ?????? ???, ?? ?????? ????? ???? ?? ??? ??? ?????? ?? ???????? ?? requery. "?? ??????? ????????? ????????? ???? ?? ???? ???." ?? ??????? ????? ??????? ?????

??? ????????????, ?? ?? ????? ??? ??AddNew, ????????ADO ?? ???????Recordset???????? ?? ???? Excel ??????, ADO ???? ?? ?? ????????? ???????? ???? ?? ??? ???? ????? Excel ??? ???? ??? ???????? ?? ???? ??????????? ??????? ?? ???, ???? ?? ???? ?????? ?? ????? ?? ???? ?? Microsoft ???????? ??? ?????::
314763  (http://support.microsoft.com/kb/314763/EN-US/ ) FIX: Excel ??? ???? ????? ??? ADO ??????? ????
?????

?? ???? ??????? ???? ????? ?? ???? Excel ???? ?? ????? ??? ?????????? ???? ?? ??????? ??????? ??? "??????" ?? "???????" apart existence ?? ???? ???? ??; ??? Excel ?????????? ??? ?? ???? ???? ??? ?? (??????) ????? ??? ??? ?? ??? ???? ???? ???????, ?? ????:
  1. ?? ??? ??? ?? ??????? ??????? ?? ?????, ?? ???? ????? ?????? ????? ??????? ???? ??:
    ???? ???? ?? ?? ?????? ??? ???? ??? ??? ?? ?? ISAM ?????? ??????? ???? ???
    ?? ???????? ???-??? ????? ?? ??????? ?? ???? blanking ?? ???? ?? ?????? ??? ???? ????
  2. Excel ?? ???? ????? ???? ???? ?? ??? ?? ?????, ?? ???? ????? ?????? ????? ??????? ???? ??:
    ?? ?????? ??? ???????? ?? ?????? ???? ???
  3. ?? ?? ??? ???? ?? ???? ?? ???? ??, ?? ?? ????? ????????? ?? ??? ???? ???? ?????? ????????? ???? ?? ??? ???? recordset ?????? ???? ?????????? row(s) ?? ???? ??? ?????
ADO ?? ??? ???? ?? Excel ??? ?????? ?? ???? ??? ??? ???????: ?? ?? ADO ?? Excel ??? ???? ?? ??? ???????? ????, ??? ?? ??? ?? ??? ?????? ?? ???? ??? ?? ?? ???? ?? ??? ??? ??? ??? ??? ???????? ???? ?? ???? ???
????? ?? ???? ????

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

ADO ?? ??? ???? Excel ???? ????? (???????? ?? ?????) ?? ?????? ?? ???? ??? ???? ??????? ?? ???? ??? ??????? ?????? ??? ???? ?? ?? OLE DB ???????, ?? ??? ?????? ????? ?? ???? ?????? ?? ??????? ?????? ????? ?? ?? ?? ?? ?????? ?? ???????? ?? ??????? ??? ?? ???? ???OpenSchemaADO ?? ???????????????????, ?????? ??? ADO ???? ??Recordset???????? ??? ?? ?? ????? ?? ???? ?? ????? Microsoft ActiveX ???? ???????? ????????? ???? ???????? ???? ?? ??????? (ADOX) ????????? ?? ??? ?? ???????? ?? ???? ???? Excel ???? ????? ?? ????? ???, ???? ??? "??????" ???? ?????????? ?? ?????? ??? ??, ?? ?? "??????" ??????? datatypes ????? ?????? ??? ?? ?? ?? ?? ???????? ???? ???? ?????? ???

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

??????? ??????? ??????? ??? ?????? ???????? (??????, ?????, ???????? ???????????? ?? ??? forth), ???? Excel ???? ????? ?? ???? ?????? equivalents, ?????????? ?? ???????? ????????? ????????? ????????????? ??? ????????? ?? consisting exposes. "????????" ?? ??? ??? ???????? ????????? ???? ?? ??? ??? ?? ???????????? "?????? ????????" ?? ??? ??? ???? ??, ?? ?? "table_type" ?? ??? ?? ???? ??????? ?? ???? ??? ????? ?????? ?????? ??????? ???? ??? ?? ?????? ?????? ???????? ??? ????? ??? ?? ??? ????????????? ?? ?? ???? ??:
Set rs = cn.OpenSchema(adSchemaTables)
??? ??????? ???? ?? (9) ?????, ?? ?? ?? populates ???? ?? ??? ?? recordset ????? (4):

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

ODBC ??????? ??? ?? ?? (9) ?????, ?? ?? ?? populates ???? ?? ??? ?? recordset ???? ??? (3):

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

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

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

  • ???????? (ADO datatype 5, adDouble)
  • ?????? (ADO datatype 6, adCurrency)
  • ??????? ?? boolean (ADO datatype 11, adBoolean)
  • (ADO datatype 7, adDate ??? ?? ????? ?? ??? ??; 135, adDBTimestamp ODBC ?? ????? ????) ??????
  • ??? (??? ADO ad...Char ??????, ???? 202, adVarChar, 200, adVarWChar ?? ????)
15 (?? Excel ??? ?????? ??????? ??) ?? ??? ??? ???? ???????? ????? ?? ??? numeric_precision ?? ????? ?? ??; ??? ??? ????? ?? character_maximum_length 255 (?? ?????? ???????? ??????, ????? ???? ?? ?????? ?????, ??? ?? ???? Excel ????? ??? ???? ??) ?? ??? ??? ????? ???? ??? ??? ?? ??? ??????? ?? ???? ??? ?? ???? ?????? ????? ??????? ???? ??data_type???? ?? ????? ??? ?? ??? ???? ?????? ??? ?????? ???????? ?? ???? ??????:
Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, "TableName", Empty))
??? ??????? ?? ???? ?? ?????? 28 ?????, ?? ?? ?? populates ?? recordset ??????? (8) ???????? ?????? ?? ?????? (9) ??? ????? ?? ??? ?? ???? ?????? ????? ?? ?? ???? ??:

  • table_name
  • column_name
  • ordinal_position
  • data_type
ODBC ??????? ???? ?? recordset 29 ?????, ?? ?? ?? populates ???? ?? (10) ???????? ????? ?? ??? ????? ?? ??? 11 ?? ???? ?????? ????? ???? ??? ???? ?? ??? ????

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

???????? ?? ???? Excel ???? ????? ?? ???????? ?? ???? ??? ??????? ?????? ???????? ??? ??????? ?? ???? ???? ?? ??? Visual Basic ??? (???? ?? ????? ?????) ?? ????? ???? ?? ????? ?? ????? outputs ?? ???? ??????? ???? ?? ?????, 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

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

??? ?? Visual Basic ???? ????? ????? ??? ???? Excel ???? ????? ?? ??? ??? ???? ???? ????? ???, ?? ???? ????? ????? ???? ???? ???? ?????? ????????? ??????? ?? ???? ??? ?? ?? ?? ??????? ????????? ???? ???? In particular, note that the Jet Provider lists both worksheets and named ranges under "Tables," where the ODBC Provider shows only named ranges. If you are using the ODBC Provider and have not defined any named ranges, the "Tables" list will be empty.

Excel Limitations

The use of Excel as a data source is bound by the internal limitations of Excel workbooks and worksheets. These include, but are not limited to:

  • Worksheet size: 65,536 rows by 256 columns
  • Cell contents (text): 32,767 characters
  • Sheets in a workbook: limited by available memory
  • Names in a workbook: limited by available memory
????? ?? ???? ????

??????

For additional information about how to use ADO.NET to retrieve and modify records in an Excel workbook with Visual Basic .NET, click the following article number to view the article in the Microsoft Knowledge Base:
316934  (http://support.microsoft.com/kb/316934/EN-US/ ) How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
295646  (http://support.microsoft.com/kb/295646/EN-US/ ) How To Transfer Data from ADO Data Source to Excel with ADO
246335  (http://support.microsoft.com/kb/246335/EN-US/ ) How To Transfer Data from ADO Recordset to Excel with Automation
247412  (http://support.microsoft.com/kb/247412/EN-US/ ) ???????: Visual Basic ?? Excel ??? ???? ??????????? ???? ?? ??? ???????
278973  (http://support.microsoft.com/kb/278973/EN-US/ ) : ????? ????????? ???? ?? ExcelADO ?? ????? ?? ??????????????? ?? Excel ??? ???? ????? ADO ?? ????? ???? ????
318373  (http://support.microsoft.com/kb/318373/EN-US/ ) Visual Basic .NET ??? GetOleDbSchemaTable ???? ?? ????? ?? Excel ?? ???????? ??????? ???? ????
????? ?? ???? ????
???? ???? ???? ??:
  • 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 KbMthi
????? ?? ???? ????
???? ?????? ???????????? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:257819  (http://support.microsoft.com/kb/257819/en-us/ )
????? ?? ???? ????