3条回答 默认 最新
- JaGuar_DeFrock_ 2021-10-07 17:17关注
用access自动获取excel里的数据的方法就是通过代码的方式导入。
首先,新建一个工程,在工程中引用如下对象ADO对象(用于连接ACCESS数据库,可用其它方式),添加一个窗体(from1),在窗体上添加如下控件:
两个文本框,用来显示EXCEL文件路径和ACCESS路径;
四个扭钮,两个用来游览,另两个是导入和退出;
两个通用对话框控件,用来打开ACCESS和EXCEL文件,一个进度条控件,用来显示导入进程。
该实例的导入是将ACCESS数据库中表的字段名单独存放在另外一个表中,导入时按表中所存字段名的顺序进行导入,不是按EXCEL表的顺序导入,这样在实际中是很实用的.
因为好多时候EXCEL表中字段顺序和ACCESS中字段顺序有可能不是一致的.
代码如下:Dim v Option Explicit Private Sub cmdLoad_Click() Dim excel_app As Object Dim excel_sheet As Object If txtExcelFile.Text = "" Then MsgBox "请选择EXCEL表" Else Dim new_value As String Label2.Caption = "正在导入,请稍候..." Screen.MousePointer = vbHourglass DoEvents '' Create the Excel application. Set excel_app = CreateObject("Excel.Application") '' Uncomment this line to make Excel visible. excel_app.Visible = True '' Open the Excel spreadsheet. excel_app.Workbooks.open FileName:=txtExcelFile.Text '' Check for later versions. If Val(excel_app.Application.Version) >= 8 Then Set excel_sheet = excel_app.ActiveSheet Else Set excel_sheet = excel_app End If Dim u ''求EXCEL表中记录的条数,以便控制进度条 u = 1 Do If Trim$(excel_sheet.Cells(u, 1)) = "" Then Exit Do u = u + 1 Loop bar.Max = u - 1 strSQL = "select * from TestValues" yourRecord.open strSQL, myConn, adOpenDynamic, adLockOptimistic ''打开记录集 Dim sql As String sql = "select * from fields order by xue" myRecord.open sql, myConn, adOpenDynamic, adLockBatchOptimistic ''打开字段记录集 myRecord.MoveFirst '' Get data from the Excel spreadsheet and insert '' it into the TestValues table. Dim v ''导入记录,用了两层循环 v = 1 Do If Trim$(excel_sheet.Cells(v, 1)) = "" Then Exit Do ''外层, yourRecord.AddNew Dim i For i = 1 To myRecord.RecordCount '' Get the next value. new_value = Trim$(excel_sheet.Cells(v, i)) '' See if it''s blank. ''If Len(new_value) = 0 Then Exit Do '' Insert the value into the database. Dim bb As String bb = myRecord("name") yourRecord(bb) = new_value myRecord.MoveNext Next i bar.Value = v v = v + 1 myRecord.MoveFirst Loop yourRecord.Update '' Comment the rest of the lines to keep '' Excel running so you can see it. '' Close the workbook without saving. excel_app.ActiveWorkbook.Close False '' Close Excel. excel_app.Quit Set excel_sheet = Nothing Set excel_app = Nothing myRecord.Close yourRecord.Close Set myRecord = Nothing Set yourRecord = Nothing Label2.Caption = "导入完毕" Screen.MousePointer = vbDefault MsgBox "共导入" & Format$(v - 1) & "条记录" End If End Sub Private Sub Command1_Click() Unload Me End Sub Private Sub Command2_Click(Index As Integer) ''寻找ACCESS数据库 CommonDialog1.Filter = "ACCESS 文件(*.mdb)|*.mdb" CommonDialog1.CancelError = True CommonDialog1.ShowOpen txtAccessFile.Text = CommonDialog1.FileName End Sub Private Sub Command3_Click() ''寻找excel数据库 CommonDialog2.Filter = "excel 文件(*.xls)|*.xls" CommonDialog2.CancelError = True CommonDialog2.ShowOpen txtExcelFile.Text = CommonDialog2.FileName End Sub Private Sub Form_Load() Call Module1.lianjie txtAccessFile.Text = datapath End Sub 模块(module1)中的代码如下: Public myConn As New ADODB.Connection ''定义连接字符串 Public myRecord As New ADODB.Recordset ''定义记录集(字段) Public yourRecord As New ADODB.Recordset ''定义记录集(数据) Public cntoad As Boolean ''是否正常连接 Public ml ''姓名字段所在列 Public strSQL ''查询字符串 Public MyDatabase As Database ''定义数据库名 Public MyTable As TableDef, MyField As Field ''定义表名和字段名 Public xuehao ''读取字段序号 Public goshiRecord As New ADODB.Recordset ''定义记录集(公式) Public hxfyn As Boolean Public hxfbds '' 公式或条件 Public duan ''要统计的字段 Public islinshi As Boolean ''是否为临时公式 Public leiRecord As New ADODB.Recordset ''定义记录集(工资类别) Public datapath As String ''数据库路径及名 Public table As String ''工资表名 Public lei As String '' 工资类别 Public Sub lianjie() ''打开数据库 On Error Resume Next myConn.Close Dim mySQL As String ''设定连接字符串 mySQL = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" mySQL = mySQL + "Data Source=" & datapath myConn.ConnectionString = mySQL ''设定连接 myConn.open ''打开连接 myRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection myRecord.CursorLocation = adUseClient goshiRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection goshiRecord.CursorLocation = adUseClient yourRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection yourRecord.CursorLocation = adUseClient End Sub
本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 无用评论 打赏 举报
悬赏问题
- ¥15 求解 yolo算法问题
- ¥15 虚拟机打包apk出现错误
- ¥30 最小化遗憾贪心算法上界
- ¥15 用visual studi code完成html页面
- ¥15 聚类分析或者python进行数据分析
- ¥15 三菱伺服电机按启动按钮有使能但不动作
- ¥15 js,页面2返回页面1时定位进入的设备
- ¥50 导入文件到网吧的电脑并且在重启之后不会被恢复
- ¥15 (希望可以解决问题)ma和mb文件无法正常打开,打开后是空白,但是有正常内存占用,但可以在打开Maya应用程序后打开场景ma和mb格式。
- ¥20 ML307A在使用AT命令连接EMQX平台的MQTT时被拒绝