ExcelADO ͸ԺԸ ADO 㹡ҹ ¹شҹ Excel

ź ź
Ţ (Article ID): 278973 - ԵѳǢͧ㹺
· | غ

ػ

ҧͧ ExcelADO.exe ʴ繶֧Ըաѵآ ActiveX (ADO) Ѻ Microsoft Jet OLE DB 4.0 ҹ ¹ŧشҹ Microsoft Excel

仹öǹŴҡٹǹŴͧ Microsoft::
ExcelADO.exe
ѹҧ˹: 12 ѹҤ 2000

ѺǡѺԸաôǹŴʹѺʹعͧ Microsoft ôԡŢ仹ʹٺ Microsoft Knowledge Base::
119591ԸѺʹѺʹعͧëͿҡԡ͹Ź
Microsoft ᡹ Microsoft ͿǨͺش ѹաûС ѧǨж١ѡҤʹ 觪»ͧѹ¹ŧѺ͹حҵ

˵㴨֧ ADO

ADO 㹡ö͹ѧ ʹ֧Ũҡشҹ Excel س Ѳ ʹ¼ҹ÷ӧҹѵѵѧ Excel:
  • ԷҾ÷ӧҹ. Microsoft Excel ActiveX ͡кǹ ado 㹡кǹ÷ӧҹ кѹ֡ҼԵͧ¡͡кǹ costly
  • scalability. Ѻء ͪ÷ Microsoft Excel ado ʴԸա䢻ѭ scaleable ҡ㹡èѴâشҹ
ado ö١㹡ö͹ŴԺشҹ سö ADO ٻẺٵáѺ ҧá سö͹ѧشҹ pre-formatted աѡٻẺ ҤسͧٻẺ "͹" ѧҡá سöӡÿ ¡÷ӧҹѵѵ شҹ

Specifics ԡâͧҹ OLE Ѻشҹ Excel

ҹŢͧ Microsoft Jet ö㹡Ҷ֧ٻҹẺ شҹ Excel ҹѪӴѺҶ֧Ը (ISAM) öԴ ͵ͧԴٻẺ¹͡ѺʹѺʹع ¼ Microsoft Jet 4.0 OLE DB سöкتԴͧҹ㹤سѵѺ Jet OLE DB ʹѺʹعԴͧҹŵ仹Ѻشҹ Excel ͧ Microsoft:
  • Excel 3.0
  • Excel 4.0
  • Excel 5.0
  • Excel 8.0
˵:: Excel 5.0 觰ҹŪԴͧشҹ Microsoft Excel 5.0 7.0 (95) ҹŪԴͧ觷 Excel 8.0 Ѻ 8.0 Excel ͧ Microsoft (97) 9.0 شҹ (2000) ҧͧ ExcelADO.exe شҹ Excel ٻẺ Excel 97 Excel 2000

ҧ仹ҸԵشҹ Excel 97 ( 2000) ADO :
Dim oConn As New ADODB.Connection
With oConn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties").Value = "Excel 8.0"
    .Open "C:\Book1.xls"
    '....
    .Close
End With
				
Dim oConn As New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Book1.xls;" & _
           "Extended Properties=""Excel 8.0;"""
oConn.Close
				
ẺἹõҧ駪ͤԴ

Ըշسöҧԧҧ (ͪǧ) شҹ Excel:
  • 蹧ҹ ͧ´ (ҧ, [Sheet1 $] [$蹧ҹͧѹ]) ҧشҹաҧԧѡɳйСͺªǧͧ蹧ҹ
    oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic
    					
  • ǧ ժͷ˹ (ҧ, [Table1)
    oRS.Open "Select * from Table1", oConn, adOpenStatic
    					
  • ǧ շк (ҧ, [Sheet1 $ A1: B10])
    oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic
    					
ǹǢͧҧ

Ѻشҹ Excel á㹪ǧжǹǢͧ (ͪࢵ) ¤ ǧáСͺǹ سöкhdr =㹤سѵѡСͧ͢س СͺǹǢͧá ԡ OLE DB ѵѵԪࢵŷس ( F1 ʴŴá F2 ʴŴͧ forth)

Դͧ

ᵡҧҡҹŴ µçԸкتԴͧѺ㹵ҧ Excel ԡ OLE DB ᷹ ᡹㹤 "guess" ԴͧѺŴӹǹӡѴ ӹǹ㹡᡹鹡Ỵ (8) سö¹ӹǹͧ᡹ ¡кؤҷҧ˹ (1) sixteen (16) Ѻmaxscanrowsõ駤㹤سѵͧѡСͧ͢س

Ѻҧ

ExcelADO.exe Сͺç EXE ҵðҹͧ Visual Basic, Active Server ˹ (ASP), Excel 97 ش ҹ 2000 Excel ˹ҷẺ аҹ Microsoft Access 2000 繴ѧ:

ç EXE ҵðҹͧ visual Basic
  • ExcelADO.vbp
  • Form1.frm
  • Form1.frx
ྨҹ
  • EmpData.asp
  • Orders.asp
شҹ Microsoft Excel
  • OrdersTemplate.xls
  • EmpDataTemplate.xls
  • ProductsTemplate.xls
  • SourceData.xls
ҹ Microsoft Access
  • Data.mdb

Ըա㹵ҧ

Ңͧ.exe ѧ

͵ͧç Visual Basic:
  1. Visual Basic Դ ExcelADO.vbp
  2. Project ͡ҧԧ:ҡ 駤ҡҧԧ֧Microsoft ADO ẺѺ DDL Фʹźѵ Microsoft ActiveX . ʵҧѺ ADO 2.5 ADO 2.6 ѧ͡Ѻͧس
  3. F5 ¡ ѺҸԵҡ
  4. ԡҧ 1:. ҧҧҢͧ OrdersTemplate.xls ҡ ADO 㹡ѧشҹ Դش¹㹵ҧ㹪ǧ˹شҹ ͧ͢ǧOrders_Table. It uses ADOAddNew/ûѺاmethods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADOis closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this:
    1. ámenu in Excel, select ͡˹.
    2. In the list of defined names, selectOrders_Table. Note that the defined name has grown to include the newly added records. The defined name is used, in conjunction with Excel's OFFSET function, to compute a total on the data added to the worksheet.
    3. Quit Microsoft Excel and return to the Visual Basic application.
  5. ԡҧ 2. This sample creates a copy of EmpDataTemplate.xls. It uses ADO to connect to the workbook and uses theԹmethod of the ADO connection to insert data (INSERT INTO in SQL) into the workbook. Data is added at defined ranges (or tables) in the workbook. When the data is transferred, the connection is closed and the workbook that results is displayed in Excel. After you examine the workbook, quit Microsoft Excel, and then return to the Visual Basic application.

  6. ԡSample 3. This sample creates a copy of ProductsTemplate.xls. It uses Microsoft ADO Extensions 2.1 for DDL and Security object library (ADOX) to add a new table (or a new worksheet) to the workbook. An ADOش¹is then obtained for the new table and data is added by using theAddNew/ûѺاԸա When the row additions are complete, the ADOis closed and the workbook is displayed in Excel. The workbook contains Visual Basic for Applications (VBA) macro code in theOPENevent for the Workbook. The macro runs when the workbook opens; if the new "Products" worksheet exists in the workbook, the macro code formats the worksheet and then the macro code is deleted. This technique presents a way for the Web developer to move formatting code away from the Web server and onto the client. A Web application could stream a formatted workbook that contains data to the client and allow macro code that would perform any "conditional" formatting that might not be possible in a template alone to run at the client.

    ˵:: To examine the macro code, view theThisWorkbookmodule in the VBAProject for ProductsTemplate.xls.

  7. ԡSample 4. This sample produces the same results as Sample 1, but the technique that is used to transfer the data is slightly different. In Sample 1, records (or rows) are added to the worksheet one at a time. Sample 4 adds the records in bulk by attaching the Excel table to an Access database and running an append query (or INSERT INTO..SELECT FROM) to append records from a table in the Access table to the Excel table. Once the transfer is complete, the Excel table is detached from the Access database and the workbook that results is displayed in Excel. Quit Excel, and return to the Visual Basic application.

  8. The last sample illustrates how you can read data from an Excel workbook. Select aҧ:in the drop-down list, and then clickSample 5. ˹ҵҧ Immediate ʴҢͧҧس͡ Ҥس͡շ蹧ҹ ("Sheet1 $" "Sheet2 $") Ѻҧ ˹ҵҧ Immediate ʴҢͧǧѺ蹧ҹ ôѧࡵ ǧ繵ͧ 1, 1 ͧ蹧ҹ ǧ鹷ҹͧǹ˭蹧ҹСͺ¢

    Ҥس͡㹪ǧкͪǧ˹ ˹ҵҧ Immediate ʴ੾Ъǧ鹺蹧ҹ
͵ͧҹ˹ (ASP):
  1. ҧExcelADOá㹺ҹͧ ôѧࡵ 鹷ҧѺá㹺ҹ C:\InetPut\WWWRoot
  2. Ѵ͡ҧ 仹ѧسҧ㹢鹵͹͹˹ҹ:
    • EmpData.asp
    • Orders.asp
    • Data.mdb
    • EmpDataTemplate.xls
    • OrdersTemplate.xls

  3. ʤԻ ASP 㹵ҧҧҢͧẺͧشҹ¡CopyԸաùFileSystemObject. ѺCopyԸա÷ ͹ѧҶ֧ʤԻͧաҶ֧¹ѧСͺ ASP
  4. ӷҧѧ Orders.asp (蹤 http://YourServer/ExcelADO/Orders.ASP), ôҺ ʴ¡Ѻ˹شҹ Excelҧ 1:ء Visual Basic
  5. ӷҧѧ EmpData.asp (蹤 http://YourServer/ExcelADO/EmpData.ASP), ôҺ ʴ¡Ѻ˹شҹ Excelҧ 2ء Visual Basic
(c) Microsoft Corporation 2000 Էͧ contributions Lori B. Turner, Microsoft Corporation

ҧԧ

ҡͧ÷Һ ôԡŢ仹ʹٺ Microsoft Knowledge Base::
195951HOWTO: Ẻͺ лѺا Excel ADO ҡ ASP
194124PRB: 觤׹ NULL OpenRecordset DAO Excel
193998HOWTO: ҹСʴ亹բ ASP
247412: Ըաö͹ѧ Excel ҡ Visual Basic
257819HOWTO: ADO Ѻ Excel ҡ Visual Basic VBA

سѵ

Ţ (Article ID): 278973 - Ǥش: 8 Ҥ 2554 - Revision: 3.0
Ѻ
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Active Server Pages 4.0
Keywords:
kbdownload kbautomation kbfile kbprogramming kbmt KB278973 KbMtth
¤
Ӥѭ: «ͿŴ¤ͧ Microsoft ᷹繹ѡŷ繺ؤ Microsoft պ¹ѡкŴ¤ سöҶ֧㹰ҹͧ Ңͧسͧ ҧá Ŵ¤Ҩբͺͧ ҨբͼԴҴ㹤Ѿ ٻẺҡó ǡѺóշǵҧҵԾٴԴ;ٴҢͧس Microsoft ǹѺԴͺͤҴ͹ ԴҴͤ·ԴҡҼԴҴ ͡麷Ţͧ١ Microsoft աûѺاͿŴ¤繻Ш
仹繩Ѻѧɢͧ:278973

ʹ

 

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