Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Bethlehem, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access to Excel (Readonly) (Access 2002)

    I am transferring a table in Access to Excel. This I know how to do. My problem is I once I transfer the data I would like to set the Excel file as read only. I would think this could be accomplished through Access but I just do not know the code.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel (Readonly) (Access 2002)

    Would you like to set the file as read only in Explorer, or would you like to set a password in the workbook that is needed to modify the workbook?

    To set the file as read-only in Explorer, use an instruction like this:

    SetAttrib "C:ExcelExport.xls", vbReadOnly

    To set a password in the workbook, you would need to use Automation, i.e. start and control an instance of Excel from Access. Post back if you need this.

    Note: none of these options is very secure. Users can still delete a read-only file, they'll only get an extra warning. And passwords in Excel are easily cracked.

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Bethlehem, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (Readonly) (Access 2002)

    Thanks Hans,
    Most likely setting the file read-only through Explorer would suffice. However, you got my curiousity up when you brought up the idea of setting a password in the workbok. How would you go about setting up a password in a workbook?

    What I would like to do is set up an Excel file and just update it when the data change in Access. I just don't want the users being able to modify and saving the data.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access to Excel (Readonly) (Access 2002)

    The procedure below will open an existing (unprotected) Excel workbook, set a password needed to modify the workbook, and save the workbook. Call it as follows:

    ProtectWorkbook "C:ExcelTest.xls", "Woody"

    I repeat that the protection is not secure. Users can still open the workbook read-only and save it under another name without password protection.

    Sub ProtectWorkbook(strFilename As String, strPassword As String)
    Dim objXL As Object
    Dim objWb As Object
    Dim fStart As Boolean

    On Error Resume Next

    Set objXL = GetObject(, "Excel.Application")
    If objXL Is Nothing Then
    Set objXL = CreateObject("Excel.Application")
    fStart = True
    If objXL Is Nothing Then
    MsgBox "Could not activate or open Excel!", vbCritical
    Exit Sub
    End If
    End If

    On Error GoTo ErrHandler

    Set objWb = objXL.Workbooks.Open(strFilename)
    objXL.DisplayAlerts = False
    objWb.SaveAs FileName:=strFilename, WriteResPassword:=strPassword
    objXL.DisplayAlerts = True

    ExitHandler:
    On Error Resume Next
    If Not objWb Is Nothing Then
    objWb.Close SaveChanges:=False
    Set objWb = Nothing
    End If
    If fStart = True And Not objXL Is Nothing Then
    objXL.Quit
    End If
    Set objXL = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Bethlehem, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access to Excel (Readonly) (Access 2002)

    Hans,

    Thank you for all your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •