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

[µo°Ý] Excel°ÝÃD

[µo°Ý] Excel°ÝÃD

Excel°ÝÃD
§Ú¦baccess¤¤¦³¤@­ÓTable¡A¤º¦³3­Ócolumn¤À§O¬°¤H¦W¡AÄæ¦ì­È¡A¦C¦ì­È¡A½Ð°Ý§Ú­n¦bexcel¤¤¯à¨Ì¾ÚÄæI ¦ì­È©M¦C¦ì­È®y¼Ð¦Û°ÊÅã¥Ü¤H¦WÀ³¦p¦ó¹F¦¨¡C
¤H¦W       Äæ¦ì­È       ¦C¦ì­È
¶À¥Ò¤A       15              12
§õ¤þ¤B       15              17
§õ®õ¤s       16              19

http://blog.xuite.net/hcm19522/twblog/352087114

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-10-25 11:47 ½s¿è

¦^´_ 1# ¶À§Ó¥þ


    ­Y¥Î¤@¯ë¨ç¼Æ¡I§Ú¤£·|¡A¤£¹L§Q¥ÎVBA¥i¥H«Ü®e©ö¹F¦¨§A»Ý­nªºµª®×
  1. Public Sub ex()
  2.     Dim myCon As Object
  3.     Dim myRs As Object
  4.     Dim TabName$, Sql$, i, arr
  5.     Set myCon = CreateObject("ADODB.Connection")
  6.     Set myRs = CreateObject("ADODB.Recordset")
  7.     myCon.Open "provider=Microsoft.ACE.OLEDB.12.0;" & _
  8.                "Data Source=" & ThisWorkbook.Path & "\¤H­û.accdb;" '¸ô®|¥Î¥»µ{¦¡¸ô®|¬Û¦P¸ê®Æ®w¦WºÙ¬°"¤H­û.accdb"
  9.     TabName = "¤H­û" 'Tabel ¦WºÙ
  10.     Sql = "SELECT * FROM " & TabName & "';" 'SQL»yªk¡A¦b TabName ªí³æ¤¤®³¥X©Ò¦³¶µ¥Ø
  11.     Set myRs = myCon.Execute(Sql)
  12.     arr = myRs.GetRows '¨ú¥X Recordset ªº¶µ¥Ø¦¨¬°°}¦C
  13.     For i = 0 To UBound(arr, 2)
  14.         Cells(arr(1, i), arr(2, i)) = arr(0, i) '
  15.     Next
  16.     myRs.Close
  17.     myCon.Close
  18.     Set myRs = Nothing
  19.     Set myCon = Nothing
  20. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2015-10-26 12:18 ½s¿è

¦^´_ 3# lpk187
ÁÂÁ lpk187 ¤j¤j, ¤S¾Ç¨ì¤@©Û,
¦ý     Cells(arr(1, i), arr(2, i)) = arr(0, i) '
¦ü¬°  Cells(arr(2, i), arr(1, i)) = arr(0, i)   'arr(2, i)¦C¦ì­È, arr(1, i)Äæ¦ì­È
¤§¤â»~! Á`¤§, ÁÂÁÂ!!

§Ú§ï¥Î
    Do While Not myRs.EOF
        Cells(myRs("¦C¦ì­È"), myRs("Äæ¦ì­È")) = myRs("¤H¦W")
        myRs.MoveNext
    Loop
Å¥»¡³t«×¸ûºC, ¦ý¸û¤£©ö¤â»~!

TOP

¦^´_ 4# yen956


    ÁÂÁ§󥿡A§âarr = myRs.GetRows¨ú¥X°}¦C¡A¥u¬OÅý¤H®a®e©ö¦b°Ï°ìÅܼƵøµ¡¤¤Æ[¹î¨ä¤¤ªº­È¡A¤]¨S¦³¤ñ¸û§Ö¡A
§Q©ó¾Ç²ß¦Ó¤w¡A¹ê»Ú¤WÁÙ¬O­n¥Î§Aªº¤è¦¡¥h°µ

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD