vb连接access excel步骤 excel 导入access
第一步 新建vb工程 新建数据库 第二步 引入数据库环境: ms activiteXdata OBJECTS 2.8 LIBRARY ms excel 11.0 引入显示控件: 常用的有三个 datagrid:可以直接从表格修改数据库数据 但是不能设置每行的颜色 推荐2st flexgrid:可以显示数据库数据 只读的 hflexgrid:支持ado 可以设置每行显示数据的颜色 推荐1st 第三步:编写代码 将数据库数据筛选显示到表格控件里面 ---------------------------------------------------------------------- 下面给出我写的vb连接access 和excel的源代码 ---------------------------------------------------------------------- 全部源代碼 '-------define hong------------ Dim ConStr As String Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim cn1 As ADODB.Connection Dim rs1 As ADODB.Recordset Private statestring As String Private Sub Command1_Click() Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection Dim Source As String CommonDialog1.Filter = "All Files (*.*)|*.xls" CommonDialog1.FilterIndex = 2 CommonDialog1.ShowOpen Text3.Text = CommonDialog1.FileName Source = CommonDialog1.FileName With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source=" & Source & ";Extended Properties=Excel 8.0;" .Open .Execute "INSERT INTO [toolsok] IN '" & App.Path & "toolsdemo.mdb' SELECT * FROM [Sheet1$] " .Close End With MsgBox "ok" End Sub Private Sub Command2_Click() On Error GoTo ErrHandler CommonDialog1.Filter = "All Files (*.*)|*.xls" CommonDialog1.FilterIndex = 2 CommonDialog1.ShowOpen Text3.Text = CommonDialog1.FileName Exit Sub ErrHandler: End Sub Private Sub Form_Load() Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "toolsdemo.mdb;Persist Security Info=False" '------------------change cn.Open ConStr cn.CursorLocation = adUseClient Select Case cn.State Case adStateClose statestring = "adStateClosed" Case adStateOpen statestring = "adStateOpen" End Select If statestring = "adStateClosed" Then MsgBox "CONNECT ERROR!",statestring Else End If '----------open the table------------------ rs.Open "SELECT * FROM toolsok order by 識別碼 desc",2,3 Set DataGrid1.DataSource = rs Text4.Text = rs.d DataGrid1.Refresh Call show_excel End Sub Private Function show_excel() Set cn1 = New ADODB.Connection Set rs1 = CreateObject("ADODB.Recordset") Dim XLS_FILE As String Set cn1 = CreateObject("ADODB.Connection") Set rs1 = CreateObject("ADODB.Recordset") cn1.Open "provider=Microsoft.Jet.OLEDB.4.0;" & "data source=" & App.Path & "/excel.xls;" & "Extended Properties=Excel 8.0;" rs1.Open "select * from [sheet1$]",3 Text2.Text = rs1.RecordCount Set MSHFlexGrid1.DataSource = rs1 MSHFlexGrid1.Refresh End Function VB壓縮access2003 '工程引入Microsoft Jet and Replication Objects Library Private Sub Command1_Click() Dim path,path1 As String Dim FIXDB As New JRO.JetEngine path = App.path & "basetmsdata.mdb" path1 = App.path & "basetmsdata_tmp.mdb" FIXDB.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path,_ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path1 Kill path '刪除源文件 FileCopy path1,path '生成壓縮後的源文件 Kill path1 '刪除緩存的文件 MsgBox "OK" End Sub (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |