[µ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

hcm19522

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

lpk187

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

yen956

¦^´_ 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, ¦ý¸û¤£©ö¤â»~!

lpk187

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