creating read-only files

Fat_and_immoral · December 19th, 2006, 10:07 AM

Does anybody know how to create read-only csv files within VBA? I am currently using the following line of code:

saveFilename = Application.GetSaveAsFilename(defaultPath, "CSV file (*.csv), *csv", , "Save as CSV file")

However, this doesn't prevent a user from making unwanted changes to the file that got saved.

Thank you!

maccas · December 19th, 2006, 10:54 AM

You can do this with the Scripting libaray. Example as follows:

Code:

Sub test()

Dim strSaveFilename As String

Dim scrFSO As Scripting.FileSystemObject
Dim scrFile As Scripting.File

    strSaveFilename = ""

    ' Set up Scripting variables
    Set scrFSO = New Scripting.FileSystemObject
    Set scrFile = scrFSO.GetFile(FilePath:=strSaveFilename)

    ' Set file to be read-only
    scrFile.Attributes = ReadOnly

    ' De-reference variables
    Set scrFSO = Nothing
    Set scrFile = Nothing

End Sub

To get the code to work you'll need to add a refernece to you project to Microsoft Scripting Runtime (Tools -> References...)

Maccas

Fat_and_immoral · December 19th, 2006, 02:04 PM

Thank you Maccas, but when I try to add the Scripting runtime reference, it says: "Error accessing the system registry."

I tried figuring out how I can fix this problem by going on Microsoft's website (http://support.microsoft.com/kb/269383), but they propose making changes to the registry, which will be difficult for me to do because it would involve working with a budget-constrained IT department.

Does anybody know a way to create read-only files without making changes to registries?

maccas · December 20th, 2006, 05:00 AM

Try using late binding then. Here's a reworking of the original post without the requirement to use references:

Code:

Sub test()

Dim strSaveFilename As String

Dim scrFSO As Object 'Scripting.FileSystemObject
Dim scrFile As Object 'Scripting.File

    strSaveFilename = ""

    ' Set up Scripting variables
    Set scrFSO = CreateObject("Scripting.FileSystemObject")
    Set scrFile = scrFSO.GetFile(FilePath:=strSaveFilename)

    ' Set file to be read-only
    scrFile.Attributes = 1 'ReadOnly

    ' De-reference variables
    Set scrFSO = Nothing
    Set scrFile = Nothing

End Sub

Fat_and_immoral · December 20th, 2006, 09:24 AM

It worked! Thank you so much.