Protecting Workbook on Network while Allowing VBA Updates
 

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

Protecting Workbook on Network while Allowing VBA Updates
 

Hi Kat,

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?

You could set the readonly property of the file.

2. How (using VBA) do I protect the workbook from the users making any
changes to it?

If you set the file to readonly, the user cannot save changes on top of the same
file.
Of course he might be able to change the readonly property to false from
explorer, but I suspect this will suffice.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Protecting Workbook on Network while Allowing VBA Updates
 

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.





"Jan Karel Pieterse" wrote:

Hi Kat,

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?

You could set the readonly property of the file.

2. How (using VBA) do I protect the workbook from the users making any
changes to it?

If you set the file to readonly, the user cannot save changes on top of the same
file.
Of course he might be able to change the readonly property to false from
explorer, but I suspect this will suffice.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Protecting Workbook on Network while Allowing VBA Updates
 

Hi Kat,

1. What code should be used to make the workbook file read only with VBA?

VBA has the SetAttr function, example:
SetAttr "TESTFILE", vbHidden + vbReadOnly ' Set hidden and read-only

2. Is there code that can save an updated report to this same file, even if
a user has it open?

I haven't tested this with VBA, but using explorer, I could open a file set as
readonly and whilst open in Excel was able to delete the file in Explorer.
So I'd guess you should be able to use the same code that created the file.

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?

You mean you want to append to the file?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Protecting Workbook on Network while Allowing VBA Updates
 

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




"Jan Karel Pieterse" wrote:

Hi Kat,

1. What code should be used to make the workbook file read only with VBA?

VBA has the SetAttr function, example:
SetAttr "TESTFILE", vbHidden + vbReadOnly ' Set hidden and read-only

2. Is there code that can save an updated report to this same file, even if
a user has it open?

I haven't tested this with VBA, but using explorer, I could open a file set as
readonly and whilst open in Excel was able to delete the file in Explorer.
So I'd guess you should be able to use the same code that created the file.

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?

You mean you want to append to the file?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Protecting Workbook on Network while Allowing VBA Updates
 

Hi Kat,

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

I don't see anything obviously wrong with your code.
What I do spot is that you don't close Excel in your code. Just before you do
Set XL = Nothing

Make sure you add:
XL.Quit

Maybe that helps?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Protecting Workbook on Network while Allowing VBA Updates
 

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

"Jan Karel Pieterse" wrote:

Hi Kat,

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

I don't see anything obviously wrong with your code.
What I do spot is that you don't close Excel in your code. Just before you do
Set XL = Nothing

Make sure you add:
XL.Quit

Maybe that helps?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Protecting Workbook on Network while Allowing VBA Updates
 

Hi Kat,

I followed all of your other suggestions too, and now the code achieves the
goal exactly as hoped.

Excellent, thanks for letting me know!

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com