如何将Access中的一张表里的数据导出到指定Excel文档,从Excel第11行开始导出
Access中有一张表,叫Apple,现在要把这张表中的数据全部导出到一个指定的Excel文件中,这个Excel文件一共有2个Sheet,要导出的Sheet名称为“ExportApple”,而且“ExportApple”这个Sheet前面10行已经有数据了,这10行数据必须保留不能覆盖的,只是把Apple表里的数据从Excel的第11行开始写入。
我下面写了一些代码,但是中间如何将Apple表的数据从Excel的第11行开始写入我就不知道怎么写了,谁能帮帮忙啊?不胜感激!!!
Function Export() Dim objApp As Object Dim objWorkbook As Object Dim objWorksheet As Object Dim objRange As Object Set objApp = CreateObject("Excel.Application") Set objApp = New Excel.Application Set objWorkbook = objApp.Workbooks.Open(Mainform!FilePath) Set objWorksheet = objWorkbook.Sheets("ExportApple!") objApp.Visible = False objApp.AutoCorrect.Application.DisplayAlerts = False '将Apple表中的数据写入到Excel文件中,从Excel的第11行开始写起,请教大家帮忙 '…… '…… '…… '…… '…… objWorkbook.Save objWorkbook.Close objApp.Quit Set objWorksheet = Nothing Set objWorkbook = Nothing Set objApp = Nothing End Function
Sub Export() Dim objApp As Object Dim objWorkbook As Object Dim objWorksheet As Object Dim objRange Dim db As DAO.Database Dim tblRst As DAO.Recordset Set db = DBEngine.Workspaces(0).Databases(0) Set tblRst = db.OpenRecordset("AppleTab") Set objApp = CreateObject("Excel.Application")' Set objApp = New Excel.Application objApp.Visible = False Set objWorkbook = objApp.Workbooks.Open("C:\Users\abc\Downloads\Access.xlsx") Set objWorksheet = objWorkbook.Sheets("ExportApple") '将Apple表中的数据写入到Excel文件中,从Excel的第11行开始写起,请教大家帮忙 Set objRange = objWorksheet.Cells(11, 1) objRange.CopyFromRecordset tblRst, 1 objWorkbook.Save objWorkbook.Close objApp.Quit Set objWorksheet = Nothing Set objWorkbook = Nothing Set objApp = Nothing Set tblRst = Nothing Set db = NothingEnd Sub
[解决办法]
楼上代码一个笔误:objRange.CopyFromRecordset tblRst后面不需要参数,不知道为何无法编辑自己的帖子。
Sub Export() Dim objApp As Object Dim objWorkbook As Object Dim objWorksheet As Object Dim objRange Dim db As DAO.Database Dim tblRst As DAO.Recordset Set db = DBEngine.Workspaces(0).Databases(0) Set tblRst = db.OpenRecordset("AppleTab") Set objApp = CreateObject("Excel.Application")' Set objApp = New Excel.Application objApp.Visible = False Set objWorkbook = objApp.Workbooks.Open("C:\Users\abc\Downloads\Access.xlsx") Set objWorksheet = objWorkbook.Sheets("ExportApple") '将Apple表中的数据写入到Excel文件中,从Excel的第11行开始写起,请教大家帮忙 Set objRange = objWorksheet.Cells(11, 1) objRange.CopyFromRecordset tblRst objWorkbook.Save objWorkbook.Close objApp.Quit Set objWorksheet = Nothing Set objWorkbook = Nothing Set objApp = Nothing Set tblRst = Nothing Set db = NothingEnd Sub