[µo°Ý] VBA ¥ÎSQL ¤@¦¸·s¼W¦hµ§¸ê®Æ¦ÜEXCELªº¤èªk

PKKO

¤p§Ì·|¥ÎVBA+SQL³s½u¦ÜMSSQL ¨Ã¥B¤@¦¸·s¼W¦hµ§¸ê®Æ,¥Î;³s±µinsert¦r¦ê§Y¥i
¦ý¤£¾å±o­n¦p¦ó³s½u¦ÜEXCEL¤§«á¤@¦¸·s¼W¦hµ§¸ê®Æ?

¤èªk¤@:µL®Ä
strSQL = "INSERT INTO " & Table_name & " VALUES (" & Columns_name & ")"
strSQL2=strSQL & ";" & strSQL

¤èªk¤G:µL®Ä
strSQL = "INSERT INTO " & Table_name & " VALUES (" & Columns_name & "),(" & Columns_name & ");"

¨D¤j¤j­Ì¨ó§U = =

lpk187

¦^´_ 1# PKKO


    ­è¦n¦³¬Ý¹L¤@½g¡A¦ý¥¼´¿¹ê½î¹L¡A§A¥i¥H¸Õ¸Õ
https://support.microsoft.com/en ... nto-microsoft-excel

PKKO

¦^´_ 2# lpk187


    ¥i±¤¦¹½g©|¥¼´£¨ì,·PÁ±z!

michael101772

¤£¤ÓÀ´§Aªº·N«ä¡A§A¬O­n±qExcel¼´sql¸Ì­±ªº¸ê®Æ¶Ü¡H
³o­Ó¥Îselect´N¥i¥H¤F¡C

baomin

¦^´_ 1# PKKO

§A¦n
        strSQL = "UPDATE PURTD1008 set TD012='" & Range("E" & I) & "' where TD001='" & Range("A" & I) & "' and TD002='" & Range("B" & I) & "' and TD003='" & Range("C" & I) & "'"
¥ÎUPDATE¡A§A¦A¸Õ¸Õ¬Ý

PKKO

¦^´_ 5# baomin


    ±z¦n,update ¬O­×§ï¸ê®Æ
¤p§Ì·Q­nªº¬O·í§A¦³¤@¸Uµ§¸ê®Æ­n¿é¤J¦ÜEXCEL
¦ý·Q­n¥ÎSQL³s½uªº¤è¦¡°µ¿é¤J
­n¦p¦ó¤@¦¸¿é¤J¦hµ§¸ê®Æ
¦Ó¨Ã«D¨C¤@µ§¸ê®Æ°õ¦æ¤@¦¸

lpk187

¥Î³o­Ó¨Ò¤l§a¡I1¸Uµ§¸ê®Æ¬ù3¬í¡C³o­Óinsert into ªº¥Îªk
¶×¤J¸ê®Æ¡A¤£¤@©w±o¥ÎSQL»y¥y

1. Public Sub ex()
   
2.     sql = "SELECT * FROM TblStudent ;"
   
3.     strSqlInstance = "¦øªA¾¹¦WºÙ"
   
4.     strSqlDB = "¸ê®Æ®w¦WºÙ"
   
5.     strSqlUser = "sa"
   
6.     strSqlPWD = "a123456"
   
7.     sConnect = "Provider=SQLOLEDB"
   
8.     sConnect = sConnect & ";Data Source=" & strSqlInstance & ";Initial Catalog=" & strSqlDB
   
9.     sConnect = sConnect & ";User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"
   
10.     Set myCon = CreateObject("ADODB.Connection")
    
11.     myCon.Open sConnect
    
12.     Set rng = Range("A2")
    
13.     With New ADODB.Recordset
    
14.         .Open sql, myCon, , adLockOptimistic
    
15.         Do Until rng = ""
    
16.             .AddNew
    
17.             For i = 1 To .Fields.Count - 1
    
18.                 .Fields(i) = rng.Offset(, i - 1)
    
19.             Next
    
20.             Set rng = rng.Offset(1, 0)
    
21.         Loop
    
22.         .Update
    
23.     End With
    
24.     Set myRs = Nothing
    
25.     myCon.Close
    
26.     Set myCon = Nothing
    
27. End Sub

½Æ»s¥N½X

¦^´_ 6# PKKO

PKKO

¦^´_ 7# lpk187


lpk¤j¤j±z¦n,
­º¥ý«D±`·PÁ±z,¥t¥~·Q½Ð±Ð¤@¤U:
±z¬O§_·|¥ÎSQL³s½u¦Ü¦³³]©w¶}±Ò±K½XªºEXCELÀɮשO?
¥H¤U¬O§Úªº»yªk(¥i³s½u¦ÜEXCELÀÉ®×,¥i¬O¤@¦ýEXCEL¦³³]©w¶}±Ò±K½X´NµLªk³s½u¤F,¤£ª¾¹D­n¦p¦ó¿é¤J¶}±Ò±K½X
¥i§_À°¤p§Ì¬Ý¤@¤U...

1. Public Conn As ADODB.Connection
   
2. '«O¦s©M§ó·s°ò¦¸ê°T
   
3. Public Sub Conn_conection()
   
4. address1="EXCELÀɮצì¸m"
   
5. If Conn Is Nothing Then Set Conn = New ADODB.Connection
   
6. '¥H¤U«Ø¥ß¼Æ¾Ú®w³s±µ
   
7. With Conn
   
8. strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;" & _
   
9.           "Data Source=" & address1 & ";Extended Properties='Excel 12.0;IMEX=0'"
   
10. .Open strConn
    
11. End With
    
12. End Sub

½Æ»s¥N½X

lpk187

¦^´_ 8# PKKO


    ¦bmicrosoftªº¤åÀɤ¤¦³´£¨ì¡A¦b [³s½u] ¯Á¤Þ¼ÐÅÒ¤W¡AÂsÄý¦Ü¬¡­¶Ã¯ÀɮסC½Ð©¿²¤¡u¨Ï¥ÎªÌ ID¡v©M¡u±K½X¡v¶µ¥Ø¡A¦]¬°¥¦­Ì¤£·|®M¥Î¦Ü Excel ³s½u (±zµLªk¶}±Ò¨ã¦³±K½X«OÅ@ªº Excel ÀÉ®×°µ¬°¸ê®Æ¨Ó·½¡C
https://support.microsoft.com/zh ... visual-basic-or-vba

©Ò¥H¥u¦³µL±K½X«OÅ@ªºexeclÀɤ~¯à°µ¬°¸ê®Æ¨Ó·½¡I

PKKO

¦^´_ 9# lpk187


    lpk ¤j¤j«D±`·PÁ±z
§Ú·Q¤]¬O³o¼Ë,¦]¬°§Ú¤w¸g¹Á¸Õ±Npassword ©ñ¨ì¥ô¦ó¦ì¸m,³£µLªk¶i¦æ³s½u
¦ý¬OAccess«o»´ÃP¹F¦¨

¦A¦¸·PÁ±zªº¦^ÂÐ!