不打开另一工作薄之工作表,提取数据至access
rivate Sub CommandButton6_Click() ‘删除access中相同日期的数据
On Error Resume Next
Dim SQL$
Dim cnn, rs, wb, mth, mytable
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.recordset")
Set wb = ThisWorkbook
mth = ThisWorkbook.Path & "\hhtj.mdb"
mytable = Left(wb.Name, InStr(wb.Name, ".") - 1) & "销售汇总表"
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mth
SQL = "DELETE * from " & mytable & " where 日期=#" & TextBox23.Value & "#"
rs.Open SQL, cnn, 1, 3
MsgBox "成功删除当次日期资料"
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
Private Sub CommandButton7_Click() '插入另一工作薄数据表的数据至access
On Error Resume Next
Dim SQL$, s$
Dim cnn, rs, wb, mth, mytable, thisyear
thisyear = Year(VBA.Date) '取出年份数字
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.recordset")
Set wb = ThisWorkbook
mth = ThisWorkbook.Path & "\hhtj.mdb"
mytable = Left(wb.Name, InStr(wb.Name, ".") - 1) & "销售汇总表"
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mth
s = "[Excel 4.0;Database=" & ThisWorkbook.Path & "\" & mytable & ".xls].[" & thisyear & "$] "
SQL = "INSERT INTO 05707销售汇总表 select * from " & s & "where 日期=#" & TextBox23.Value & "#"
rs.Open SQL, cnn, 1, 3
MsgBox "成功插入当次日期资料"
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
网友评论