how can I open in read only mode a query

[Pedro Lerma]

Hi group
I have a query that i send a excel sheet, but I want that this excel sheet
is opened in read only mode so that the user cannot modify it.

Thanks
Pedro

 

[Jeff Boyce]

Pedro

Access can send the (results of) a query to Excel. Are you expecting Access
to set Excel to be read-only? This might be possible via automation, if you
are willing to do some coding...

Have you looked into opening the Excel spreadsheet after it has received the
data from Access, and "locking" it (from within Excel, not from within
Access)? This would be a much simpler solution, and would not require any
coding.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pedro Lerma]

Hi Jeff
Access can send the (results of) a query to Excel. Are you expecting Access

to set Excel to be read-only? Yes

look my code

Private Sub Query_Click()
On Error GoTo Err_Query_Click

Dim stDocName As String

stDocName = "queryexample"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.RunCommand acCmdOutputToExcel
DoCmd.Close

Exit_Query_Click:

But when I add readonly=true option after acCmdOutputToExcel, this send me
error message

 

[Jeff Boyce]

Pedro

We can't see the error message.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pedro Lerma]

Sorry, the error is Wrong number of arguments or invalid property assignment
in DoCmd.RunCommand line.
I don't know if I'm using the appropriate instruction.

Private Sub Query_Click()
On Error GoTo Err_Query_Click

Dim stDocName As String

stDocName = "queryexample"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.RunCommand acCmdOutputToExcel, ReadOnly = True
DoCmd.Close

Exit_Query_Click

 

[Douglas J. Steele]

I'm not sure there is a readonly option using RunCommand, but if there is,
try:

DoCmd.RunCommand acCmdOutputToExcel, ReadOnly := True

If that doesn't work, try using TransferToSpreadsheet to create the
spreadsheet, and then use Automation to open that spreadsheet, using the
read-only option when you do:

Dim objExcel As Object
Dim strFile As String
Dim strDocName As String


strFile = "C:\Folder\File.xls"
strDocName = "queryexample"
DoCmd.TransferSpreadsheet acExport, , strDocName, strFile
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open strFile, ReadOnly:=True
objExcel.Visible = True

 

[Jeff Boyce]

Have you checked Access HELP for the exact syntax? Perhaps what you want to
do ("ReadOnly=True") is not part of that command...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pedro Lerma]

I want to do a excel sheet read only from a query.

 

[Jeff Boyce]

Pedro

I believe I understand what you want to do. I'm not sure the command allows
that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pedro Lerma]

ok don't worry, I'm looking in this site:
http://support.microsoft.com/kb/302460/en-us

thanks