Protecting Workbook on Network while Allowing VBA Updates

[Kat]

I am using VBA in Access to send a recordset to an excel workbook, to format
the workbook, and to save the workbook on a network drive (to be updated
several times a day using the task scheduler to run the AutoExec macro which
runs the code.)
Users will open and read the report from the network file.
1. Do I need to save the excel file under a new name each time, in case the
previous report is open by a user when the macro runs? If so, is there a way
to delete the files from the network drive using VBA? If not, how do I
ensure there will be now errors?
2. How (using VBA) do I protect the workbook from the users making any
changes to it?
(A strategy that minimizes message boxes on the user end is preferred, as
possible.)

 

[Kat]

Thank you. I've tried to make the excel workbook read only and have some
more questions:
1. What code should be used to make the workbook file read only with VBA?
2. Is there code that can save an updated report to this same file, even if
a user has it open?
3. What code provides Write access the saved and closed excel workbook
file, so that updates can save as a workbook to the same file name?

My Attempt:
1. I added a WriteResPassword in the SaveAs section:
xl.ActiveWorkbook.SaveAs Filename:= _
"X:\Folder\ExcelReport.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="password",
ReadOnlyRecommended:= _
False, CreateBackup:=False

2. If I leave this workbook open and try to rerun the code, I get error:
Runt-time erro '1004':
Cannot access "EcatRetain5.xls'.

3. If I close and reopen this workbook as read only and then try to run the
report update, the excel vba does not work and gives the error:
Run-tiem error '1004':
Operation failed. 'X:\folder\ExcelReport.xls' is write reserved.

 

[Kat]

Hi Jan,
Thank you very much. The SetAttr works to make the file read only.
The report in the excel file needs to be overwritten everyday with a new
report with the same file name.
The code is works the first time it is run with a file name, but not after
that....the main code is below...any additonal input would be very
appreciated ... Kat

Function SendReport()

‘Objects:
Dim MyRecordset As ADODB.Recordset
Dim xl As Excel.Application
Dim xlwkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet

‘Start a new recordset
Set MyRecordset = New ADODB.Recordset
MyRecordset.Open "qryReport", CurrentProject.Connection, adOpenStatic

‘make workbook
Set xl = New Excel.Application
Set xlwkbk = xl.Workbooks.Add
xl.Visible = True
Set xlsheet = xlwkbk.Worksheets.Add
xlsheet.Name = "ReportTab"
With xlsheet
xl.Range("A3").CopyFromRecordset MyRecordset
End With
xl.Application.ScreenUpdating = False
xl.Application.DisplayAlerts = False

‘Save
xl.ActiveWorkbook.SaveAs Filename:= _
"X:\Folder\ExcelReport", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

‘Close
xl.ActiveWorkbook.Close

‘Make read-only and hidden
SetAttr "X:\Folder\ExcelReport", vbReadOnly + vbHidden

xl.Application.ScreenUpdating = False
xl.Application.DisplayAlerts = False

Set MyRecordset = Nothing
Set xl = Nothing
Set xlwkbk = Nothing
Set xlsheet = Nothing

End Function

 

[Kat]

Hi Jan,
Thank you!
I added the xl.Quit line.
I followed all of your other suggestions too, and now the code achieves the
goal exactly as hoped.
Kat