none
Encrypt Excel 2013 Workbook Using VBA From Outside of Excel RRS feed

  • Question

  • Not sure exactly which forum to post to, hope this one is OK.

    I am exporting data from my Access 2013 database into an Excel workbook (.xlsx).  This I am doing using Access macros rather than VBA, but it could be done either way.

    Now I have a requirement to password-protect the resulting workbook.  I think this should be possible using the Excel 2013 object model, right from VBA code in my Access database.  I have researched quite a bit, and can't seem to find any examples.  I've found msdn articles about encryption providers, but don't quite understand them.

    Could somebody please post some example code showing how to encrypt a workbook once I have it open (using VBA)?  I'm already fairly familiar with using the object model, and opening workbooks in code.  Just not sure about the encrypting part.

    Thanks...


    Ron Mittelman

    Thursday, October 29, 2015 3:46 AM

All replies

  • Hi Ron,

    We can use Workbook.Protect method to protect a workbook so that it cannot be modified. You can get more detail about this method from link below:
    Workbook.Protect Method (Excel)

    >>Could somebody please post some example code showing how to encrypt a workbook once I have it open (using VBA)?  <<

    A good way to learn how to get the sample code for object models of Word, Excel, and Powerpoint, based on what you want to do as a user, is to use the Macro Recorder.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 30, 2015 1:42 AM
    Moderator
  • Now I have a requirement to password-protect the resulting workbook.

    Call Workbook.SaveAs to set a password that must be a entered to open the workbook.

    Andreas.

    Sub Test()
      Dim xlApp As Object 'Excel.Application
      Dim xlWb As Object 'Excel.Workbook
      Const xlWorkbookDefault = 51
      
      Set xlApp = CreateObject("Excel.Application")
      Set xlWb = xlApp.Workbooks.Add
      xlWb.SaveAs "Z:\test.xlsx", xlWorkbookDefault, "MyPassword"
      xlWb.Close
      xlApp.Quit
    End Sub
    

    Saturday, October 31, 2015 10:42 AM
  • Thanks Fei, that's a good idea.  I'll try that.  However, I am not sure the Workbook.Protect is what I want.  I don't want to protect against modification, I want to encrypt so sensitive data can't be intercepted.

    Based on the articles I read, it seems that there is a difference between encrypting a workbook and simply adding a password.  Do you think that's true?  The articles seem to say a certificate provider is needed.


    Ron Mittelman

    Saturday, October 31, 2015 10:15 PM
  • Thanks Andreas.

    I am not sure the SaveAs with password is the same thing as encrypting the workbook.  It seems you can password protect without encrypting.  We have security concerns, and the customer wants high-level encryption.  Does the method you describe do this (in Office 2013)?


    Ron Mittelman

    Saturday, October 31, 2015 10:18 PM
  • I am not sure the SaveAs with password is the same thing as encrypting the workbook. 

    We have security concerns, and the customer wants high-level encryption.  Does the method you describe do this (in Office 2013)?

    a) No. When you protect a Workbook (using the botton from the ribbon) protects only the structure of the file, not the data.

    b) AFAIK the file encryption uses "AES 128-bit advanced encryption"
    https://support.office.com/en-us/article/Password-protect-documents-workbooks-and-presentations-ef163677-3195-40ba-885a-d50fa2bb6b68

    Andreas.


    Monday, November 2, 2015 8:11 AM