Dilemma w/Updating Read-Only Workbooks

[mhague]

Hello - Hopefully someone can offer a suggestion to this problem I have.

I currently have VBA code that opens a master workbook and then updates 20 departmental workbooks with data from the master. The departmental workbooks are read-only as I don't want my users to change them.
I remove the read-only attribute in my program so I can save the workbook once I've got it open in my program. Problem is, if one of the users is viewing the workbook, my program cannot access it to update it.

Is there a way to way I can keep the departmental workbooks read-only so the users can't change them, but my VBA program can open save them - even if they are being viewed at the time the program runs? I read about shared workbooks, but I don't think this is what I want. I admit I don't know that much about the intricacies of Excel...

Thanks,

Marcie

 

[Colo]

Welcome Marcie! SetAttr Function makes it possible.

<PRE><FONT color=red>Sub </FONT>SetAttrtest()

<FONT color=#339966> 'Change here to your file path
</FONT>
<FONT color=red>Const </FONT>strWbPath <FONT color=red>As</FONT><FONT color=red> String</FONT> = "c:book1.xls"

<FONT color=#339966> 'When you open via vba Make read only file to Normal file.
</FONT>
SetAttr strWbPath, vbNormal

<FONT color=#339966> 'Yourcode
</FONT>
<FONT color=#339966> 'Book open and Something you want
</FONT>
<FONT color=#339966> 'Save workbook
</FONT>


<FONT color=#339966> 'After close book, Make Normal file to read only file.
</FONT>
SetAttr strWbPath, vbReadOnly

<FONT color=red>End Sub</FONT>
</PRE>

 

[mhague]

Thank you! I will give this a try!