如何自动从 Access 2007 导出到 Excel 2003
我想自动将 Access 查询的结果导出到 Excel。我的环境是：
- Access 2007
- Access 2003格式的数据库
- 导出应该是Excel 2003格式。
该查询包括一个备注列，最多可包含 512 个字符。
到目前为止，我已尝试以下操作：
在 Access 中运行查询，然后将结果网格复制/粘贴到 Excel 中。这工作正常，但需要手动步骤。
运行一些使用 DoCmd.TransferSpreadsheet 导出查询的 VBA 代码，如下所示：
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery", "MyFile.xls"
我遇到的问题是 VBA 代码将 Memo 列截断为 255 个字符。
在不进行此截断的情况下以编程方式导出到 Excel 的最简单方法是什么？
理想情况下，我想将数据从 Access“推送”到 Excel，而不是相反，即我没有尝试从 Excel 中使用“导入外部数据”。
编辑
针对目前的评论和回复：
可以导出包含备注字段的表格吗？
我没有尝试过导出表格，因为这样做对我没有任何帮助。
...截断备忘录的事情之一，例如对其进行排序
查询确实包含 ORDER BY 子句，因此结果已排序（并且必须排序）。但它没有在备忘录栏中排序。
可以通过使用 Left(MyMemo, 4096) 等处理备忘录来避免这种情况。
查询中的备注列已使用“Left(Replace(MemoColumn, "...", "..."), 512)”之类的内容进行处理并截断为 512 个字符。所以使用 Left(...) 似乎没有帮助。
尝试使用 copyfromrecordset 函数实现自动化
我尝试过将 Excel Automation 与 Range.CopyFromRecordSet 结合使用。在这种情况下，较长的备注字段不会被截断，而是会在末尾导出一些垃圾字符。
I want to automate exporting the results of an Access query to Excel. My environment is:
- Access 2007
- A database in Access 2003 format
- Export should be in Excel 2003 format.
The query includes a Memo column that can contain up to 512 characters.
So far I've tried the following:
Run the query in Access, then copy/paste the result grid into Excel. This works fine but requires a manual step.
Run some VBA code that exports the query using DoCmd.TransferSpreadsheet as follows:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery", "MyFile.xls"
The problem I have is that the VBA code truncates the Memo column to 255 characters.
What's the easiest way to programatically export to Excel without this truncation?
Ideally I want to "push" the data from Access to Excel rather than the other way around, i.e. I have not attempted to use "Import External Data" from within Excel.
Edit
In response to the comments and response so far:
Can you export a table that contains a memo field?
I haven't tried exporting a table, since being able to do so wouldn't help me anyway.
... one of the things that truncates memos, like sorting on it
The query does contain an ORDER BY clause, so results are sorted (and have to be sorted). But it's not sorted on the memo column.
That can be avoided by processing the memo with something like Left(MyMemo, 4096).
The memo column in the query is already processed and truncated to 512 characters using something like "Left(Replace(MemoColumn, "...", "..."), 512)". So using Left(...) doesn't seem to help.
Try automating using the copyfromrecordset function
I've tried using Excel Automation with Range.CopyFromRecordSet. In this case the longer Memo fields are not truncated, but instead are exported with some garbage characters at the end.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试使用 copyfromrecordset 函数进行自动化。虽然我不记得 255 个字符有任何问题，但限制可能仍然存在。 模块：通过自动化将记录传输到 Excel
运行代码后，我将建议使用后期绑定删除 Excel 引用。 Microsoft Access 中的后期绑定
Try automating using the copyfromrecordset function. While I don't recall any problems with 255 characters that restriction might still exist. Modules: Transferring Records to Excel with Automation
Once you have the code running then I'd suggest removing the Excel reference by using late binding. Late Binding in Microsoft Access