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

[µo°Ý] ±qexcel §ì¨úaccess¥Ø«e¨Ï¥Î¨ìªº½s¸¹«á¦Û°Ê½s5½X

[µo°Ý] ±qexcel §ì¨úaccess¥Ø«e¨Ï¥Î¨ìªº½s¸¹«á¦Û°Ê½s5½X

½Ð±Ð¦U¦ì¥ý¶i~

¥Ø«e¥i¥H±qexcel³]­p«ö¶s§âaccessªº¸ê®Æ¶×¤J¨ìexcel
¦ý·Q­nŪ¨úaccess¤¤¨Ï¥Î¨ìªº½s¸¹«á
¦bexcel¦Û°Ê½s5½X¤U¥h

¨Ò¦p:[access]
¦WºÙ        ID        ½s¸¹
·s¤@        105111        1
¤p¤G        105222        2
¤p¤T        105333        3
¸v¥|        105444        4
¤ý¤­        105555        5
¤»¤»        105666        6
¤C¤C        105777        7
¤K¤K        105888        8
¤[¤[        105999        9

½s¸¹¨Ï¥Î¨ì9
©Ò¥H§Æ±æ¦bexcel¥i¥H¦b½s¸¹Äæ¦ìª½±µÅã¥Ü10-14Åý¨Ï¥ÎªÌ¤H¤u¿é¤J¸ê®Æ
·Q½Ð±Ð³o­n«ç»ò¥´~©Î¬O±Ð¾Ç¤@¤UÅÞ¿è~ÁÂÁÂ

´ú¸ÕÀÉ®×: DATA.zip (34.93 KB)

§Ú¹Á¸Õ¥Î¥H¤U¥N½X¸Ñ¥X¨Ó¤F
¤èªk¦³ÂI²Â
  1. Sub autonumber()
  2.   Dim arr, i%
  3.   Dim myCon As Object, myRs As Object, sql$

  4.    
  5.     USE = Worksheets("¤u§@ªí1").Range("A65536").End(xlUp).Row
  6.     Worksheets("¤u§@ªí1").Range("A2:C" & USE).ClearContents
  7.     Set myCon = CreateObject("ADODB.Connection")
  8.     Set myRs = CreateObject("ADODB.Recordset")
  9.     myCon.Open "provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.Path & "\test.accdb;"

  10.     sql = "SELECT * FROM ¸ê®Æªí1 ORDER BY ¸ê®Æªí1.½s¸¹;"
  11.     Set myRs = myCon.Execute(sql)

  12.     Do While Not myRs.EOF
  13.              vl = myRs.Fields(2).Value
  14.             Sheets(1).Cells(2, 1) = vl + 1
  15.             Sheets(1).Cells(3, 1) = vl + 2
  16.             Sheets(1).Cells(4, 1) = vl + 3
  17.             Sheets(1).Cells(5, 1) = vl + 4
  18.             Sheets(1).Cells(6, 1) = vl + 5
  19.         myRs.MoveNext
  20.     Loop
  21.    
  22.   Application.EnableEvents = True
  23. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¯à¥I¥X·R¤ß´N¬OºÖ¡A¯à®ø°£·Ð´o´N¬O¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD