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

[µo°Ý] ADO Select°ÝÃD

[µo°Ý] ADO Select°ÝÃD

¤j®a¦n¡A¤p§Ì³Ìªñ¦b±µÄ²VBA¤º¨Ï¥ÎADO¨Ó¨ú±o¸ê®Æ¡C

³oÃ䦳­ÓºÃ°Ý¡A¦b§Úªº¼ÐÃDÄ欰¤é´Á¡A¨ú±o·|¦³°ÝÃD...

¸ê®Æ¨Ó·½¡G
01.jpg
2019-11-4 22:59


¼gªk¦p¤U¡G
  1. Sub ado_test()

  2. Dim cnn As Object, rst As Object
  3. Dim Mypath As String, Str_cnn As String
  4. Dim sql As String, i As Long

  5. Set cnn = CreateObject("adodb.connection")
  6. Mypath = ThisWorkbook.FullName

  7. If Application.Version < 12 Then
  8.     Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath
  9. Else
  10.     Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath
  11. End If

  12. cnn.Open Str_cnn

  13. '---------------------------------'
  14. sql = "select ¶µ¦¸,¾÷¥x¦WºÙ,2019/1/1 from [¤u§@ªí2$] where ¾÷¥x¦WºÙ='A01'"

  15. Set rst = cnn.Execute(sql)

  16. Range("A1").CurrentRegion.ClearContents

  17. For i = 0 To rst.Fields.Count - 1
  18.     Cells(1, i + 1) = rst.Fields(i).Name
  19. Next i

  20. [A2].CopyFromRecordset rst


  21. cnn.Close

  22. Set cnn = Nothing
  23. Set rst = Nothing

  24. End Sub
½Æ»s¥N½X
°õ¦æµ²ªG¡G
02.jpg
2019-11-4 23:01


¼ÐÃDÄ檺¤é´Á³¡¤À¡A¤£¯à¥¿½T¦C¥X¡A¥B¤U¤è¼Æ­È±a¥X¿ù»~¡C

­Y¬O¨Ï¥Î select * from [¤u§@ªí2$] where ¾÷¥x¦WºÙ='A01'

¥i¥H¥¿½T¦C¥X
¡A¦ý¥Ñ©ó§Úªº¼ÐÃDÄ欰¤é´Á¡A¤@¦~¤é´Á365¥[¤W¤@¨Ç«e¸mªº¼ÐÃD¬ù¦³375¡C

¦Ó»yªk¤¤ªºExecute(sql)¡A¼ÐÃDÄæFields.Count³Ì¦h¥u¦³255¡C

¦³¿ìªk¸Ñ¨M¶Ü¡H

¦^´_ 1# »a³·
±N2019/1/1«e«á¥[¤W[ ]¸Õ¸Õ
EX:[2019/1/1]

TOP

¦^´_ 2# jcchiang

¸g¹L¹Á¸Õ¡A¥L·|Åã¥Ü¤@­Ó¿ù»~°T®§¡C

µL­È´£¨Ñµ¹¤@©Î¦h­Ó¥²­n°Ñ¼Æ¡C

²§±`°T®§

²§±`°T®§.png
2019-11-30 09:06

²§±`³¡¤À.png
2019-11-30 09:06
   

¬Ý¨Ó¬O¼W¥[ [ ]¡A©Ò³y¦¨ªº¡C

TOP

¦^´_ 3# »a³·

ExeclÄæ¦ìÃþ§O½Ð§ï¦¨¤å¦r

TOP

¦^´_ 4# jcchiang


    ¦³¡A§Ú¦³¹Á¸Õ¹L±N¼ÐÃDÄ檺¤é´Á­×§ï¦¨¤å¦r¹L¡C

    ¦ý¬O¡A¥u­n±N [ ] ¥[¤W¡A¥L´N·|¸õ¥X¤W­±©ÒÅã¥Üªº¿ù»~°T®§¡C

TOP

¦^´_ 5# »a³·

SQLÄæ¦ì¦WºÙ¬°¤é´Á.zip (17.49 KB)

TOP

¦^´_ 6# jcchiang


°õ¦æ¤W¬O¨S¦³°ÝÃD¤F¡C

¤£¹L¦b¼W¥[¤é´Á³¡¤Àµy¤£¤è«K¡A¥Ñ©ó¬O¤å¦r³Ì«á©ì¦²·|Åܦ¨2019/1/365 ...¡C

¤£¹LÁÂÁ±zªº¸Ñ´b¡A¯uªº«ÜÁÂÁ¡C
---

¥t¥~ÁÙ·Q½Ð±Ð¡A­Y¬O¸ê®Æfield±q2019/1/1 ~ 2019/12/31¡C

¸ê®Æ¦A§ä¨ì8¤ë©³¤§«áªº¸ê®Æ³£load¤£¥X¡A³o¦³¿ìªk¸Ñ¨M¶Ü¡H

TOP

¦^´_  jcchiang


°õ¦æ¤W¬O¨S¦³°ÝÃD¤F¡C

¤£¹L¦b¼W¥[¤é´Á³¡¤Àµy¤£¤è«K¡A¥Ñ©ó¬O¤å¦r³Ì«á©ì¦²·|Åܦ¨201 ...
»a³· µoªí©ó 2019-12-3 21:37


§Ú¥Î¤½¦¡¸Ñ¨M¤é´Á©ì¦²°ÝÃD¤F...

§Q¥Î =text(D1,"yyyy/m/d") §Y¥i¥H¸Ñ¨M¡A»Ý­n²Ä¤G³¡¤Àªº¸Ñµª...

TOP

¦^´_ 7# »a³·
³o´N¬O§A©Ò»¡ªº°ÝÃD
¦Ó»yªk¤¤ªºExecute(sql)¡A¼ÐÃDÄæFields.Count³Ì¦h¥u¦³255¡C

¤£µM´N¬O¥Î¤½¦¡¥h·j´M©Ò»Ý¸ê®Æ
=HLOOKUP("2019/12/10",¤u§@ªí2!C:NC,MATCH("A02",¤u§@ªí2!B:B),FALSE)

=HLOOKUP("¬d¸ß¤é´Á",­n¬d¤é´Áªº½d³ò¦ì¸m,MATCH("¾÷¥x¦WºÙ",­n¬d¾÷¥x¦WºÙªº½d³ò¦ì¸m),FALSE)

TOP

¥»©«³Ì«á¥Ñ »a³· ©ó 2019-12-4 20:21 ½s¿è

¦^´_ 9# jcchiang


¦b¦P¤@¥÷workbook¤º¥i¥H¥Î¤½¦¡¡C

¦]¬°ªø´Á¨Ï¥Îªº±¡ªp¤U¡A¸ê®Æ¶q·|«ÜÃe¤j¡C

¦Ó¤½¦¡§PÂ_¦¡¤U¦h¤F¤§«á¡AÁöµM¨ú±oªº¸ê®Æªí­±¤W¤£¨ì10­Ó¦r¤¸¡C

¥i¬O¨ä¹ê¤½¦¡¥e«Ü¦h¡A±`±`¨ì«á­±ÀÉ®×´N«ÜªÎ¤j¡C

¨ä¹ê¡A¥D­nªº¬O...

§Ú·Q¦b¥t¥~¤@¥÷workbook°õ¦æADO¡A³z¹LADO¤£¶}±ÒÀɮרӨú±o¸ê®Æ¡C

³o¼ËÀɮפ]´N¤£¦Ü©ó¤Ó¤j...

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD