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

[µo°Ý] EXCEL§å¦¸·s¼W¸ê®Æ¦ÜACCESS¥¢±Ñ

[µo°Ý] EXCEL§å¦¸·s¼W¸ê®Æ¦ÜACCESS¥¢±Ñ

±z¦n¡A¤p§Ì±N®Ñ¥»½d¨Ò®M¦Ü­Ó¤HÀɮ׮ɡAµo²{¤@ª½¶]¨ì©³¤Uµ{¦¡´NÂ_±¼..
¦ý¤@ª½¤£¸Ñ­ì¦]¡A¦A³Â·ÐÀ°¦£¸Ñ´b¡C

OFFICEª©¥»¡G2010
ACCESS¸ê®Æªí¦WºÙ¡GDATA
EXCEL¸ê®Æªí¦WºÙ¡GACC

Sub ¶×¤JAccess()

Dim AdoConn As New ADODB.Connection
Dim strConn As String
Dim DataAddr As String
Dim strSQL As String
Dim strVer 'As String


strVer = Worksheets("Main").Cells(3, 16)
DataAddr = Sheets("ACC").Range("A1:O" & Sheets("ACC").Range("A" & Cells.Rows.Count).End(xlUp).Row).Address(0, 0)
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data source= C:\Users\OO\Desktop\OO.accdb"
AdoConn.Open strConn
DataAddr = Sheets("ACC").Range("A1:O" & Sheets("ACC").Range("A" & Cells.Rows.Count).End(xlUp).Row).Address(0, 0)
strSQL = "Insert Into [DATA] Select *," & strVer & "As ª©¥» From [Excel 12.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].[ACC$" & DataAddr & "]"
AdoConn.Execute strSQL
strSQL = "Select ª©¥»,¤ë¥÷,¼Æ¶q from [DATA] where ª©¥»=" & strVer & "Order By ¤ë¥÷"
Sheets("ACC2").Range("A1").CopyFromRecordset AdoConn.Execute(strSQL)
AdoConn.Close
MsgBox "¸ê®Æ¤wÀx¦s"

End Sub


¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C

¸É¤W²§±`µe­±

[ BB.JPG
¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C

TOP

¦^´_ 2# yifan2599
§Aªºµ{¦¡½X ¥[¤W Debug.Print  ¦b§Y¥@¹Bºâµøµ¡,¬d¬Ý§Aªº¦¡¤l¿ù¦b­þ¸Ì
  1. strSQL = "Insert Into [DATA] Select *," & strVer & "As ª©¥» From [Excel 12.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].[ACC$" & DataAddr & "]"
  2. Debug.Print strSQL   
  3. Stop
  4. AdoConn.Execute strSQL
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD