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

[µo°Ý] ½Ð°Ý¦p¦ó±NSQL¸ê®Æªí¤¤¬Y¤@Äæ¸ê®Æ½Æ»s¨ìEXCEL©O?

[µo°Ý] ½Ð°Ý¦p¦ó±NSQL¸ê®Æªí¤¤¬Y¤@Äæ¸ê®Æ½Æ»s¨ìEXCEL©O?

¥»©«³Ì«á¥Ñ algoji3ji3 ©ó 2018-10-30 19:57 ½s¿è

¦U¦ì¤j¤j½Ð°ÝCopyFromRecordset rs·|±N¾ã­Ó¸ê®Æªí¿é¥X¨ìEXCEL¤W¡A¦ý¬O¦pªG§Ú¥u­n¨ä¤¤¤@¾ãÄæ¸ê®Æ¸Ó«ç»ò§ï©O?
  1. Private Sub CommandButton2_Click()

  2. '©w¸q³¡¥÷
  3. Dim cn As ADODB.Connection 'Excel³s±µ
  4. Dim rs As ADODB.Recordset '±o¨Óªºµ²ªG,·|©ñ¨ì¥ß³o­Órsªº¦a¤è.
  5. Dim strDataSrcXlsPath As String '¼Æ¾Ú®wEXCEL¦ì¸m
  6. Dim strQuery As String '¥Î§@®³¼Æ¾ÚªºSQL
  7. Dim strStartlocation As String '©w¸q±o¨Ó¼Æ¾Ú,©ñ¨ì¨º¤@­Ó¦ì¸m.

  8. 'I) ¥H¤U«Ø¥ß¼Æ¾Ú®w³s±µ
  9. strDataSrcXlsPath = ThisWorkbook.Path & "\¸ê®Æ.xlsx" '©w¥ß¼Æ¾Ú®wEXCEL¦ì¸m

  10. Set cn = New ADODB.Connection

  11. With cn
  12. '½T©w³s±µ©Ò¥ÎªºLibrary
  13. .Provider = "Microsoft.ACE.OLEDB.12.0"
  14. .CursorLocation = adUseClient
  15. '§â³s±µ«ü¦V«ü©wExcel
  16. 'xtended properties¬°excelªºª©¥», ³q±`¬°12 ©Î 8,
  17. '12¬°2007ExcelÀÉ(¦pXLSX,XLSM). 8¥i¬Û®e1997-2003µ¥excel(XLS)
  18. .ConnectionString = "Data Source=" & strDataSrcXlsPath & _
  19. ";Extended Properties=Excel 12.0;"
  20. '¶}©l³s±µ,³o­Ó®É­ÔACE·|¥´¶}excel
  21. .Open
  22. End With

  23. 'II) ¥H¤U¹B¦æSQL©M¦^¶Ç¼Æ¾Ú
  24. '®³¥X¤@¦æ¸ê®Æ,©Ò¥ÎSQL¬[ºc¤j¬ù¬°
  25. 'SELECT ... FROM ... WHERE
  26. '
  27. 'SELECT ¤Wªº * ¥Î§@«ü©ú®³¥X©Ò¦³ªí¤WªºÄæ¦ì
  28. 'TOP 50 ¬O«ü©â¥XÀY50¦C¼Æ¾Ú
  29. '
  30. 'FROM ±qDATASHEET¤u§@ªí©â¥X
  31. 'strQuery = "SELECT top 10 * FROM [Á`ªí$]"
  32. strQuery = "SELECT ¯¸§O¤ÀªR,Ãþ§O,¤ë¥÷,¤é´Á,SUM(²£¥X¼Æ) AS Á`¼Æ " & _
  33.            "FROM [Á`ªí$] " & _
  34.            "where ¤é´Á between #2018/9/1# and #2018/9/30# " & _
  35.            "GROUP BY ¯¸§O¤ÀªR,Ãþ§O,¤ë¥÷,¤é´Á HAVING Ãþ§O='305AS'" & _
  36.            "ORDER BY ¯¸§O¤ÀªR,¤é´Á"
  37.       
  38. 'strQuery = "SELECT ¤é´Á " & _
  39.            "FROM [Á`ªí$] " & _
  40.            "where ¤é´Á between #2018/9/1# and #2018/9/30# " 'HAVING Ãþ§O='305AS'"

  41. Set rs = cn.Execute(strQuery) '°õ¦æSQL

  42. 'III) §â¼Æ¾Ú§Û¦Ü©Ò»Ýªº¦ì¸m
  43. strStartlocation = "A1"

  44. 'III-1 §âÄæ¦ì¦WºÙ¤@­Ó¤@¦a®³¥X
  45. 'rs.Fields.Count : Äæ¦ì¼Æ¼Æ
  46. 'rs.Fields(n).Name : Äæ¦W
  47. Dim lngColCounter As Long
  48. i = -1
  49. For lngColCounter = 0 To rs.Fields.Count - 1
  50.     Range(strStartlocation).Offset(0, lngColCounter) = rs.Fields(lngColCounter).Name
  51. Next lngColCounter
  52. 'III-2 §â¼Æ¾Ú®w¸ê®Æ¦Cªí®³¥X

  53. ActiveSheet.Range(strStartlocation).Offset(1, 0).CopyFromRecordset rs

  54. 'IV) Ãö±¼/²M²z³s±µ
  55. cn.Close
  56. Set cn = Nothing
  57. Set rs = Nothing

  58. End Sub
½Æ»s¥N½X
«ü©wÄæ¶×¥X.rar (66.69 KB)

¦^´_ 1# algoji3ji3
¥i¥H¦b¸ê®Æ®w»yªk¨ºÃäselect§A­nªº¯S©wÄæ¦ì,¦b¥þ³¡¥á¨ìexcel¤W­±!
As you sow, so shall you reap.

TOP

¤j¤j§Ú´«­Ó°Ýªk~

¤U­±³o¬q«ü¥O¨Æ±N¾ã­Ó¼Æ¾Ú®w©ñ¦b"A1"ªº¦ì¸m
ActiveSheet.Range(strStartlocation).Offset(1, 0).CopyFromRecordset rs

¨º¦pªG§Ú­n¤À§O±N¼Æ¾Ú®w¤¤ªº¦U"¾ãÄæ"¤À§O©â¥X©ñ¨ì¦U§O«ü©w¦ì¸m©O?

TOP

        ÀR«ä¦Û¦b : ¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤Hµh­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD