ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¤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 = =
PKKO

¦^´_ 1# PKKO


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

TOP

¦^´_ 2# lpk187


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

TOP

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

TOP

¦^´_ 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¸Õ¸Õ¬Ý

TOP

¦^´_ 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¤@µ§¸ê®Æ°õ¦æ¤@¦¸
PKKO

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2017-2-19 20:20 ½s¿è

¥Î³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

TOP

¦^´_ 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
PKKO

TOP

¦^´_ 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

TOP

¦^´_ 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ªº¦^ÂÐ!
PKKO

TOP

        ÀR«ä¦Û¦b : ºw¤ô¦¨ªe¡C²É¦Ì¦¨ÅÚ¡A¤Å»´¤vÆF¡A¤Å¥Hµ½¤p¦Ó¤£¬°¡C
ªð¦^¦Cªí ¤W¤@¥DÃD