Protecting Workbook on Network while Allowing VBA Updates

  • Thread starter Thread starter Kat
  • Start date Start date
K

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.)
 
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.
 
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
 
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
 
Back
Top