Exporting query as an excel sheet - read only

D

David9746

I have a query that is exported out to and excel sheet uising VBA code
"DoCmd.transferspreadsheet". I am trying to find out if it is possible to
export this query to an excel sheet and make that excel sheet read only when
it is exported and each time it is opened.
 
A

Arvin Meyer [MVP]

David9746 said:
I have a query that is exported out to and excel sheet uising VBA code
"DoCmd.transferspreadsheet". I am trying to find out if it is possible to
export this query to an excel sheet and make that excel sheet read only
when
it is exported and each time it is opened.
Click to expand...

Here is some code from Dev Ashish which I've altered to add a Protect
command to protect the sheet. Protecting a sheet makes it read-only for
users, except that the file can still be deleted. Here's the code:

'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish


Sub sCopyFromRS()
'Send records to the first
'sheet in a new workbook
'
Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenRecordset("tblStock", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
.Protect "Your Password Here"
End With

End With
End If
End Sub
 
D

David9746

Arvin Meyer said:
Here is some code from Dev Ashish which I've altered to add a Protect
command to protect the sheet. Protecting a sheet makes it read-only for
users, except that the file can still be deleted. Here's the code:

'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish


Sub sCopyFromRS()
'Send records to the first
'sheet in a new workbook
'
Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenRecordset("tblStock", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
.Protect "Your Password Here"
End With

End With
End If
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...

Arvin

I believe this will work for me.

Thank You
 
M

mem

Is there a way to take this further and password protect the workbook being
transfered instead of just the ability to protect the worksheet?

Thanks!
 
Top