Exporting Query as Shared Workbook

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 ma开发者_开发知识库nually 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

This doesn't seem to be among your options. I don't see how to do it. Hopefully you can address this at the system level (in other words, get them all on Access).

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`