一个access的窗体程序,窗体中有4个按钮,是处理一个CSV文件的,代码是其中一个按钮的程序 Option Compare DatabasePrivate Sub Form_Load() '*窗体加载时,设置连接字符串并且连接数据库 strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source" & _ "=" + Access.CodeProject.Path + "\數據處理.mdb" & _ ";Persist Security Info=False" objConn.Open strConnection End SubPrivate Sub Form_Unload(Cancel As Integer) '*窗体关闭时,釋放對象 Set objRs = Nothing objConn.Close Set objConn = Nothing End SubPrivate Sub 命令9_Click() On Error Resume Next '*定义Excel对象 Dim VBExcel As Excel.Application Dim xlbook As Excel.Workbook Dim xlsheet As Excel.Worksheet '*定义FileSystemObject对象 Dim fs, f1 Dim s1 As Stringpgs1.Visible = True pgs1.Max = 100 '*删除原有数据 strSQL = "delete from bomcom" objConn.Execute strSQL '*对比变更数据 strSQL = "insert into bomcom (b_parentid,b_childid,b_reference,b_reference1,b_number,b_number1) select n_parentid," & _ "n_childid,oldtbl.o_reference,n_reference,oldtbl.o_number,n_number from newtbl" & _ " inner join oldtbl on newtbl.n_allid = oldtbl.o_allid" & _ " where n_reference <> o_reference or n_number <> o_number" objConn.Execute strSQL strSQL = "update bomcom set b_def = '变更' where b_def = '0'" objConn.Execute strSQL pgs1.Value = 40 '*对比新增数据 strSQL = "insert into bomcom (b_parentid,b_childid,b_reference,b_number) select n_parentid,n_childid,n_reference" & _ ",n_number from newtbl where newtbl.n_allid not in (select o_allid from oldtbl)" objConn.Execute strSQL strSQL = "update bomcom set b_def = '新增' where b_def = '0'" objConn.Execute strSQL pgs1.Value = 70 '*对比删除数据 strSQL = "insert into bomcom (b_parentid,b_childid,b_reference1,b_number1) select o_parentid,o_childid,o_reference" & _ ",o_number from oldtbl where oldtbl.o_allid not in (select n_allid from newtbl)" objConn.Execute strSQL strSQL = "update bomcom set b_def = '删除' where b_def = '0'" objConn.Execute strSQL pgs1.Value = 100 pgs1.Visible = False Set VBExcel = CreateObject("excel.application") '*创建FileSystemObject对象,删除BOOK3.CSV文件并创建该文件 Set fs = CreateObject("Scripting.FileSystemObject") fs.deletefile Access.CodeProject.Path + "\book4.csv" Set f1 = fs.createtextfile(Access.CodeProject.Path + "\book4.csv", forwriting, False) s1 = "ParentID,ChildID,OldReference,NewReference,OldNumber,NewNumber,Reason" f1.writeline s1
'*写入数据 strSQL = "select * from bomcom order by b_parentid desc,b_childid" objRs.Open strSQL, objConn Do While Not objRs.EOF s1 = objRs("b_parentid") & "," & objRs("b_childid") s1 = s1 + ",""" & objRs("b_reference1") & """," + """" & objRs("b_reference") & """," s1 = s1 & objRs("b_number1") & "," & objRs("b_number") & "," & objRs("b_def") f1.writeline s1 objRs.MoveNext Loop objRs.Close
Set f1 = Nothing Set fs = Nothing '*显示csv文件 Set xlbook = VBExcel.Workbooks.Open(Access.CodeProject.Path + "\book4.csv") Set xlsheet = xlbook.Worksheets(1) xlsheet.Activate VBExcel.Visible = True End Sub
Option Compare DatabasePrivate Sub Form_Load()
'*窗体加载时,设置连接字符串并且连接数据库
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source" & _
"=" + Access.CodeProject.Path + "\數據處理.mdb" & _
";Persist Security Info=False"
objConn.Open strConnection
End SubPrivate Sub Form_Unload(Cancel As Integer)
'*窗体关闭时,釋放對象
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
End SubPrivate Sub 命令9_Click()
On Error Resume Next
'*定义Excel对象
Dim VBExcel As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
'*定义FileSystemObject对象
Dim fs, f1
Dim s1 As Stringpgs1.Visible = True
pgs1.Max = 100
'*删除原有数据
strSQL = "delete from bomcom"
objConn.Execute strSQL
'*对比变更数据
strSQL = "insert into bomcom (b_parentid,b_childid,b_reference,b_reference1,b_number,b_number1) select n_parentid," & _
"n_childid,oldtbl.o_reference,n_reference,oldtbl.o_number,n_number from newtbl" & _
" inner join oldtbl on newtbl.n_allid = oldtbl.o_allid" & _
" where n_reference <> o_reference or n_number <> o_number"
objConn.Execute strSQL
strSQL = "update bomcom set b_def = '变更' where b_def = '0'"
objConn.Execute strSQL
pgs1.Value = 40
'*对比新增数据
strSQL = "insert into bomcom (b_parentid,b_childid,b_reference,b_number) select n_parentid,n_childid,n_reference" & _
",n_number from newtbl where newtbl.n_allid not in (select o_allid from oldtbl)"
objConn.Execute strSQL
strSQL = "update bomcom set b_def = '新增' where b_def = '0'"
objConn.Execute strSQL
pgs1.Value = 70
'*对比删除数据
strSQL = "insert into bomcom (b_parentid,b_childid,b_reference1,b_number1) select o_parentid,o_childid,o_reference" & _
",o_number from oldtbl where oldtbl.o_allid not in (select n_allid from newtbl)"
objConn.Execute strSQL
strSQL = "update bomcom set b_def = '删除' where b_def = '0'"
objConn.Execute strSQL
pgs1.Value = 100
pgs1.Visible = False
Set VBExcel = CreateObject("excel.application")
'*创建FileSystemObject对象,删除BOOK3.CSV文件并创建该文件
Set fs = CreateObject("Scripting.FileSystemObject")
fs.deletefile Access.CodeProject.Path + "\book4.csv"
Set f1 = fs.createtextfile(Access.CodeProject.Path + "\book4.csv", forwriting, False)
s1 = "ParentID,ChildID,OldReference,NewReference,OldNumber,NewNumber,Reason"
f1.writeline s1
'*写入数据
strSQL = "select * from bomcom order by b_parentid desc,b_childid"
objRs.Open strSQL, objConn
Do While Not objRs.EOF
s1 = objRs("b_parentid") & "," & objRs("b_childid")
s1 = s1 + ",""" & objRs("b_reference1") & """," + """" & objRs("b_reference") & ""","
s1 = s1 & objRs("b_number1") & "," & objRs("b_number") & "," & objRs("b_def")
f1.writeline s1
objRs.MoveNext
Loop
objRs.Close
Set f1 = Nothing
Set fs = Nothing
'*显示csv文件
Set xlbook = VBExcel.Workbooks.Open(Access.CodeProject.Path + "\book4.csv")
Set xlsheet = xlbook.Worksheets(1)
xlsheet.Activate
VBExcel.Visible = True
End Sub