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

yifan2599

±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

¤U¸ü (55.22 KB)
»¡©ú

2018-10-22 22:04

yifan2599

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

[

¤U¸ü (13.11 KB)

2018-10-22 22:14

GBKEE

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