如题所述
以ä¸æ¯æç¨äºå®ç°ä»Excel导æ°æ®è¿ACCESSç代ç ï¼ä½ åèä¸ä¸ï¼åºè¯¥å°±å¯ä»¥ååºæ¥äºï¼ï¼ExcelåAccess两个æ件æ¾ä¸ä¸ªæ件夹ä¸ï¼
Public Sub 客æ·æ£å¸¸ä¾ä»·æ¹éç»´æ¤()
If Cells(4, 6) = "" Then
MsgBox "请å éæ©éè¦ç»´æ¤ä»·æ ¼çç³»ç»ï¼", vbInformation
Cells(4, 6).Select
Exit Sub
Else
Dim i As Integer, j As Integer, k As Integer, sht As Worksheet 'i,j,k为æ´æ°åéï¼sht 为excelå·¥ä½è¡¨å¯¹è±¡åéï¼æåæä¸å·¥ä½è¡¨
Dim cn As New ADODB.Connection 'å®ä¹æ°æ®é¾æ¥å¯¹è±¡ ï¼ä¿åè¿æ¥æ°æ®åºä¿¡æ¯ï¼è¯·å æ·»å ADOå¼ç¨
Dim rs As New ADODB.Recordset 'å®ä¹è®°å½é对象ï¼ä¿åæ°æ®è¡¨
Dim strCn As String, strSQL As String 'å符串åé
Dim mdbFile As String
On Error GoTo add_err
mdbFile = ActiveWorkbook.Path & "\DY_DATA.mdb"
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFile 'å®ä¹æ°æ®åºé¾æ¥å符串
cn.Open strCn 'ä¸æ°æ®åºå»ºç«è¿æ¥ï¼å¦ææåï¼è¿åè¿æ¥å¯¹è±¡cn
Set rs = New ADODB.Recordset
rs.Open "dblç´è¥å®¢æ·æ£å¸¸ä¾ä»·è¡¨", cn, adOpenKeyset, adLockOptimistic
k = Cells(4, 8) + 8
For i = 9 To k
If Cells(i, 7) = "" Then
Else
rs.AddNew
rs(1) = Cells(i, 2)
rs(2) = Cells(i, 7)
rs(3) = Cells(i, 8)
rs.Update
Cells(i, 7) = ""
Cells(i, 8) = ""
End If
Next
MsgBox "æ°æ®è®°å½æ·»å æåï¼", vbInformation
Cells(4, 6).Select
ActiveWorkbook.RefreshAll
add_exit:
Exit Sub
End If
add_err:
MsgBox Err() & vbCrLf & Error()
Resume add_exit
End Sub
Public Sub 客æ·æ£å¸¸ä¾ä»·æ¹éç»´æ¤()
If Cells(4, 6) = "" Then
MsgBox "请å éæ©éè¦ç»´æ¤ä»·æ ¼çç³»ç»ï¼", vbInformation
Cells(4, 6).Select
Exit Sub
Else
Dim i As Integer, j As Integer, k As Integer, sht As Worksheet 'i,j,k为æ´æ°åéï¼sht 为excelå·¥ä½è¡¨å¯¹è±¡åéï¼æåæä¸å·¥ä½è¡¨
Dim cn As New ADODB.Connection 'å®ä¹æ°æ®é¾æ¥å¯¹è±¡ ï¼ä¿åè¿æ¥æ°æ®åºä¿¡æ¯ï¼è¯·å æ·»å ADOå¼ç¨
Dim rs As New ADODB.Recordset 'å®ä¹è®°å½é对象ï¼ä¿åæ°æ®è¡¨
Dim strCn As String, strSQL As String 'å符串åé
Dim mdbFile As String
On Error GoTo add_err
mdbFile = ActiveWorkbook.Path & "\DY_DATA.mdb"
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFile 'å®ä¹æ°æ®åºé¾æ¥å符串
cn.Open strCn 'ä¸æ°æ®åºå»ºç«è¿æ¥ï¼å¦ææåï¼è¿åè¿æ¥å¯¹è±¡cn
Set rs = New ADODB.Recordset
rs.Open "dblç´è¥å®¢æ·æ£å¸¸ä¾ä»·è¡¨", cn, adOpenKeyset, adLockOptimistic
k = Cells(4, 8) + 8
For i = 9 To k
If Cells(i, 7) = "" Then
Else
rs.AddNew
rs(1) = Cells(i, 2)
rs(2) = Cells(i, 7)
rs(3) = Cells(i, 8)
rs.Update
Cells(i, 7) = ""
Cells(i, 8) = ""
End If
Next
MsgBox "æ°æ®è®°å½æ·»å æåï¼", vbInformation
Cells(4, 6).Select
ActiveWorkbook.RefreshAll
add_exit:
Exit Sub
End If
add_err:
MsgBox Err() & vbCrLf & Error()
Resume add_exit
End Sub
温馨提示:答案为网友推荐,仅供参考
第1个回答 2015-03-24
ado+sql语句
Set conn = CreateObject("adodb.connection")
conn.Open = "dsn=****** ;uid=;psw="
conn.Execute ("delete * from sheet2")
Set rs = CreateObject("adodb.recordset")
rs.CursorType = 3
rs.LockType = 3
sql_str = "select * from sheet2"
rs.Open sql_str, conn
i = 2
Do Until Sheets("导入数据").Cells(i, 1) = ""
rs.addnew
rs!zcbm = Sheets("导入数据").Cells(i, 1)
:
:
rs.Update
i = i 1
Loop
j = i - 2
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
Set conn = CreateObject("adodb.connection")
conn.Open = "dsn=****** ;uid=;psw="
conn.Execute ("delete * from sheet2")
Set rs = CreateObject("adodb.recordset")
rs.CursorType = 3
rs.LockType = 3
sql_str = "select * from sheet2"
rs.Open sql_str, conn
i = 2
Do Until Sheets("导入数据").Cells(i, 1) = ""
rs.addnew
rs!zcbm = Sheets("导入数据").Cells(i, 1)
:
:
rs.Update
i = i 1
Loop
j = i - 2
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing