Exporting Query Results to Excel File AND Setting File as READ ONL

  • Thread starter Thread starter MJ
  • Start date Start date
M

MJ

I have an associate of mine that is looking for a way to set a file that is
exported from one of our databases as READ ONLY.

The database is currently exporting the results of a query to an Excel file
just fine, but now the customer wants the file set as Read Only. Is there a
simple way to do that as the file is exported?

Thank you in advance for your assistance.
 
Here's some code that will send a recordset to Excel and protect it with a
password. It can then be opened, but only changed if one has the password.

Sub sCopyFromRS()
'Send records to the first
'sheet in a new workbook
'
Dim rs As DAO.Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Set rs = CurrentDb.OpenRecordset("qryStock", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = CreateObject("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 "Arvin"
End With

End With
End If
End Sub
 
Arvin Meyer said:
Here's some code that will send a recordset to Excel and protect it with a
password. It can then be opened, but only changed if one has the password.

Sub sCopyFromRS()
'Send records to the first
'sheet in a new workbook
'
Dim rs As DAO.Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Set rs = CurrentDb.OpenRecordset("qryStock", _
dbOpenSnapshot)
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = CreateObject("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 "Arvin"
End With

End With
End If
End Sub
Click to expand...
 
Back
Top