Access与Excel结合使用常用代码

1、Excel 使用灵活但是处理数据量小(数据一多就会卡)Access 能数据量大(相对于 Excel)但是使用不灵活。Excel 与 Access 结合使用能相互弥补不足。如用 Access 用为后台数据库对表进行筛选、汇总等一系列处理后再以 Excel 为前台导出数据，会比直接用 Excel 表刷选、汇总一个上万行的数据表快的多。以下是几种 access 与 Excel 结合使用常用方法与代码。Access 导入表格代码Sub导入材料出库表()DoCmd.SetWarnings False取消警告DoCmd.RunSQL DELETE FROM 材料出库表删除材料出库表DoCmd.Transf

2、erSpreadsheet acImport, 8, 材料出库表, _“F:材料出库明细.xls, _True, sheet1!a2:ag20000导入材料出库明细表中的 sheet1 表 a2:ag20000 区域的数据DoCmd.SetWarnings TrueEnd Sub恢复警告注： DoCmd.TransferSpreadsheet acImport, 8, 材料出库表 , “F:材料出库明细 .xls, True, sheet1!a2:ag20000 此句代码中的材料出库表是表明导入到 Access 中的材料出库表中， 参数 true 表明以导入数据的第一行作为列字段如将 true

3、 改 false 数据导入 access 中后表的列字段为“F1、F2、F3. ”。创建 Access 窗体与按钮创建窗体点创建窗体设计即调出窗体设计窗体界面。创建按钮点设计按扭即可用鼠标在窗体中插入按钮。按钮做好后会自动弹出“命令按钮向导”直接点取消。 右击按钮事件生成器代码生成器即进入此按钮的代码编辑区。将光标放到 Private Sub Command3_Click() 下面粘贴以下代码DoCmd.SetWarnings FalseDoCmd.RunSQL DELETE FROM 材料出库表 DoCmd.TransferSpreadsheet acImport, 8, 材料出库表, _

4、“F:材料出库明细.xls, _True, sheet1!a2:ag20000DoCmd.SetWarnings True关 闭 代 码 界 面 回 到 窗 体 设 计 界 面 ， 点 右 下 角 窗 体 视 图 ,再点刚做好的按钮，“2成本ERP 导出表导入 access材料出库明细.xls”的数据就会导入到 access 中的材料出库表中，将不同的代代码放入按钮中就能实现不同功能的按钮。Access 导出到 Excel 表Access 导出 Excel 表方法很多(最简单的方法直接在 Access 点导出)这里介绍以 Excel作为前台 Access 作为后台取数据的

5、方法。新建一个 Excel 打开 Alt+F11 打开VBE 窗口点右键插入模块这时会弹出模块 1 的界面并且光标在此界面闪。将以下代码贴入此模块，按F5 即可运行此模块就可从 Access 导出数据(注运行时出现警告“用户类型未定义”这是没有对 Microsoft ActiveX Data Object X Library 2.8 引用点工具引用将 Microsoft ActiveX Data Object X Library2.8 打下勾即可)Sub 导出材料汇总()Dim mydata$, SQL$, hh% Dim rs As ADODB.RecordsetDim cnn As ADO

6、DB.Connection Range(d2:l10000).Clearmydata = 2成本ERP 导出表基础数据.accdb Set cnn = New ADODB.ConnectionWith cnn.Provider = Microsoft.ACE.OLEDB.12.0.Open mydata End WithSQL = SELECT DISTINCT 材料出库表.工单号, 材料出库表.仓库, 材料出库表.领料部门, 材料出库表.物料类型, _& 材料出库表.物料名称, 材料出库表.单位, Sum(材料出库表.实发数量) AS 实发数量之总计, Sum(材料出库

7、表.金额) _& AS 金额之总计, 材料出库表.领料用途 FROM 材料出库表 GROUP BY 材料出库表. 领料部门, 材料出库表.仓库, 材料出库表.工单号, _& 材料出库表.物料类型, 材料出库表.物料名称,材料出库表.单位, 材料出库表.领料用途Set rs = cnn.Execute(SQL)With Range(Cells(1, 1), Cells(1, rs.Fields.Count).HorizontalAlignment = xlCenter End WithRange(d2).CopyFromRecordset rs Range(A1:l10000).Font.Siz

8、e = 10 Columns(g:k).Style = Comma rs.Closecnn.CloseSet rs = Nothing Set cnn = NothingEnd Sub注：Set cnn = New ADODB.Connection With cnn.Provider = Microsoft.ACE.OLEDB.12.0.Open mydata End With这段代码是建立与 Access 文件的连接。(access 文件不用打开通过后台连接 Access 的数据)SQL = SELECT DISTINCT 材料出库表.工单号, 材料出库表.仓库, 材料出库表.领料部门, 材

9、料出库表.物料类型, _& 材料出库表.物料名称, 材料出库表.单位, Sum(材料出库表.实发数量) AS 实发数量之总计, Sum(材料出库表.金额) _& AS 金额之总计, 材料出库表.领料用途 FROM 材料出库表 GROUP BY 材料出库表. 领料部门, 材料出库表.仓库, 材料出库表.工单号, _& 材料出库表.物料类型, 材料出库表.物料名称,材料出库表.单位, 材料出库表.领料用途这段代码是 SQL 查询命令，修改这段代码可将各种所需的表导出。代码 Range(d2).CopyFromRecordset rs 是表导出的起始位置，Range(d2)就是以 d2 单元格开始如改 Range(“a2”)则从 a2 单元格开始。复杂的 SQL 查询语句可以在