将查询导出为共享工作簿

发布于 2024-10-21 20:20:09 字数 288 浏览 3 评论 0原文

我有代码可以迭代我选择的查询并将它们导出为标准 .xls 文件。

我的问题是，如何将它们导出为共享工作簿？

一个工作团队使用大约 20 个工作簿，他们必须手动将它们设置为共享，以便他们都可以同时访问它们。

目前，为了输出，我运行命令：

DoCmd.OutputTo acQuery, Query, acFormatXLS, output_folder & DirectoryFriendlyQuery & ".xls", False, "", 0

原文

I've got code that'll iterate through my queries of choice and export them as standard .xls files.

My question is, how can I export these as Shared Workbooks?

A team at work uses about 20 workbooks, and they manually have to set them as shared so they all can access them at teh same time.

Currently, to output, I run the command:

DoCmd.OutputTo acQuery, Query, acFormatXLS, output_folder & DirectoryFriendlyQuery & ".xls", False, "", 0

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不弃不离 2024-10-28 20:20:09

这似乎不在您的选择范围内。我不知道该怎么做。希望您可以在系统级别解决这个问题（换句话说，将它们全部放在 Access 上）。

回复收藏 0 原文

独自唱情﹋歌 2024-10-28 20:20:09

您需要使用办公自动化。从 VBA IDE (alt+F11) >> 设置对 Microsoft Excel 对象库的引用工具>>参考。

Dim xlApp As Excel.Application, wb As Excel.Workbook`

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(FileName:=myfile)
wb.SaveAs accessmode:=xlShared, FileName:=myfile
wb.Close savechanges:=false
Set sb = Nothing
xlApp.Quit`

为了加快速度，无需保存文件两次（一次在 access 中，另一次在 Excel 中），您可以在内存中创建工作表。

Dim xlApp As Excel.Application, wb As Excel.Workbook, sh as excel.worksheet

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(FileName:=myfile)
set sh = wb.worksheets.add()
sh.cells(1,1).copyfromrecordset Data:=currentdb.openrecordset(name:="myquery")
wb.SaveAs accessmode:=xlShared, FileName:=myfile
wb.Close savechanges:=false
Set sb = Nothing
xlApp.Quit`

You'll need to use office automation. Set a reference to your Microsoft Excel object library from the VBA IDE (alt+F11) >> Tool >> References.

Dim xlApp As Excel.Application, wb As Excel.Workbook`

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(FileName:=myfile)
wb.SaveAs accessmode:=xlShared, FileName:=myfile
wb.Close savechanges:=false
Set sb = Nothing
xlApp.Quit`

To make this a bit speedier by not having to save the file twice (one in access and again in excel) you can create the worksheet in memory.

Dim xlApp As Excel.Application, wb As Excel.Workbook, sh as excel.worksheet

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(FileName:=myfile)
set sh = wb.worksheets.add()
sh.cells(1,1).copyfromrecordset Data:=currentdb.openrecordset(name:="myquery")
wb.SaveAs accessmode:=xlShared, FileName:=myfile
wb.Close savechanges:=false
Set sb = Nothing
xlApp.Quit`

回复收藏 0 原文

~没有更多了~