Lm th? no ? nh?p d? li?u t? Microsoft SQL Server vo Microsoft Excel

D?ch tiu ? D?ch tiu ?
ID c?a bi: 306125 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny


H?ng d?n t?ng b?c ny m t? cch nh?p d? li?u vo Microsoft Excel t? c s? d? li?u qun r?u, m?t c s? d? li?u m?u ?c bao g?m v?i Microsoft SQL Server.

ActiveX Data Objects (ADO) cung c?p quy?n truy c?p vo b?t k? lo?i ngu?n d? li?u. N l m?t m h?nh ?i t?ng ph?ng v?i vi ?i t?ng. Cc ?i t?ng chnh trong m h?nh ?i t?ng ADO l:
   Object          Description
   Connection      Refers to the connection to the data source.
   Recordset       Refers to the data extracted.
   Command         Refers to a stored procedure or SQL statements that 
                   need to be executed.
M?c d c r?t nhi?u cch ? tr? l?i m?t Recordset b?ng cch s? d?ng ADO, bi vi?t ny t?p trung vo k?t n?i v cc ?i t?ng Recordset.

Yu c?u

B?n ph?i c m?t my ch? ?a phng ang ch?y Microsoft SQL Server v ch?a c s? d? li?u qun r?u.

Microsoft khuy?n co b?n c ki?n th?c sau y:
  • T?o Visual Basic cho cc ?ng d?ng quy tr?nh ny trong cc chng tr?nh Office.
  • Lm vi?c v?i cc ?i t?ng bi?n.
  • Lm vi?c v?i cc ?i t?ng Excel.
  • Quan h? c s? d? li?u qu?n l? h? (RDBMS) khi ni?m.
  • C?u trc Query Language (SQL) ch?n pht bi?u.

Tham kh?o cc th vi?n ?i t?ng ADO

  1. B?t ?u Excel. M? m?t b?ng tnh m?i v sau lu n nh SQLExtract.xls.
  2. B?t ?u Visual Basic Editor v ch?n VBA d? n c?a b?n.
  3. Trn cc Cng c? tr?nh n, nh?p vo Ti li?u tham kh?o.
  4. Nh?n vo y ? ch?n phin b?n m?i nh?t c?a cc Microsoft ActiveX Data Objects Library h?p ki?m.

T?o k?t n?i

  1. Chn m?t m-un m?i vo d? n.
  2. T?o ra m?t quy tr?nh ph? m?i ?c g?i l DataExtract.
  3. G? hay dn o?n m? sau:
    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection
    ' Provide the connection string.
    Dim strConn As String
    'Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    'Connect to the Pubs database on the local server.
    strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=pubs;"
    'Use an integrated login.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    'Now open the connection.
    cnPubs.Open strConn

ang gi?i nn d? li?u

G? hay dn m? sau y ? trch xu?t cc h? s c?a b?n:
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
	' Assign the Connection object.
	.ActiveConnection = cnPubs
	' Extract the required records.
	.Open "SELECT * FROM Authors"
	' Copy the records into cell A1 on Sheet1.
	Sheet1.Range("A1").CopyFromRecordset rsPubs
	' Tidy up
End With

Set rsPubs = Nothing
Set cnPubs = Nothing

Xc minh r?ng m? lm vi?c

  1. Ch?y m?.
  2. Chuy?n sang Excel v xem xt Sheet1 trong b?ng tnh ? xem d? li?u.

Gi?i p th?c m?c

N?u m? c?a b?n s? xu?t hi?n ? treo, v b?n nh?n ?c m?t l?i th?i gian ch?y my ch? c s? d? li?u c?a b?n c th? xu?ng. B?n c th? s? d?ng ti s?n ConnectionTimeout ? ki?m sot l th?i gian ? tr? l?i m?t l?i th?i gian ch?y. Thi?t l?p ny b?t ?ng s?n ? m?t gi tr? l?n hn 0. N?u b?n thi?t l?p gi tr? b?ng khng, k?t n?i s? khng bao gi? th?i gian ra. Gi tr? m?c ?nh l 15 giy.


B?n c th? t?m th?y cc m?u m? b?ng cch t?m ki?m Web site sau c?a Microsoft:

Thu?c tnh

ID c?a bi: 306125 - L?n xem xt sau cng: 20 Thang Chin 2011 - Xem xt l?i: 3.0
p d?ng
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
T? kha:
kbhowtomaster kbmt KB306125 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:306125

Cung cp Phan hi


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