0

From MS Access, I open an Excel (.xlsx) file to apply some changes, save and upload it on Access. Everything is managed by VBA.

If the Excel file hasn't an open password encryption, VBA works perfectly.

In order to manage a Workbook encrypted with password, I have written a VBA that has many odd bugs. I give in the form the password as an input, but the password isn't read by the following code. Opening the Workbook, Excel asks me to insert the workbook password in the prompt. Then, Workbook.Unprotect doesn't unprotect the workbook that is saved with the password. As expected, Access cannot upload data from the protected Excel file.

I think should be a very easy problem to solve, but I cannot figure it out. Have you any insight to give me?

'( VBA procedure starts with variable declaration)

Set ExcelApp = CreateObject("Excel.Application")

If IsNull(Me!Password_Ins) 'Password_Ins is the input 
    Then
    Strg_Pwd = ""
    Else: Strg_Pwd = Me!Password_Ins
    End If

On Error GoTo ErrorHandler
Set WorkBookObj= ExcelApp.Workbooks.Open(Percorso, False, , , Strg_Pwd) ' asks to insert the password manually

'ExcelApp.Visible = True

WorkBookObj.Unprotect Strg_Pwd 'doesn't remove the password

' (procedure continues)
3463495
  • 13
  • 6
  • Code reads password and opens workbook for me. Now you want to remove password? – June7 Jul 12 '21 at 18:56
  • Does this answer your question? [Unprotect VBProject from VB code](https://stackoverflow.com/questions/16174469/unprotect-vbproject-from-vb-code) – June7 Jul 12 '21 at 18:56
  • My research indicates Unprotect cannot remove Open password encryption. It can remove password established for protecting workbook/worksheet structure. My testing seems to support that. – June7 Jul 12 '21 at 19:23
  • To be clear, I need to remove Open password encryption. I don't need to remove the password to the structure. – 3463495 Jul 13 '21 at 08:04
  • Can I set an open password encryption in a way I can remove it then using VBA code? – 3463495 Jul 13 '21 at 08:08
  • I solved saving the worksheet in another workbook without encryption. Now I upload on Access this second workbook. Thank you @June7 for your support. – 3463495 Jul 13 '21 at 08:37

1 Answers1

0

WorkBookObj.Unprotect removes the worksheet password. It works well. Strg_Pwd is the encryption password of the Excel file. Using as parameter of "Workbooks.Open" allows to open the file, but it is encrypted anyway. To avoid encryption, I created a new Excel file and copy&pasted the sheet.

3463495
  • 13
  • 6