Using Access 2007 module to encrypt Excel workbooks

[TraceWilliams]

I am trying to build a module to open a list of Excel 2007 workbooks stored in a table and attach a single password to each.

I will have to build a do until loop with a counter that will designate the row selection from the Access table each time through.

Does anyone have any code built to open and set the password for the workbooks?

 

[Isskint]

Hi TW

in its basic form, this will work

Sub WKbks(WkBkName as String, WkBkPassword as String)
Dim WkBk As Workbook
Set WkBk = Workbooks.Open(WkBkName)
WkBk.Password = WkBkPassword
WkBk.Save
WkBk.Close
End Sub

All you would need to do is call this form your loop supplying the workbook name (including path eg "C:\book3.xls") and the password. Oh and you would obviously need to set a reference to the relevant Excel library in access

 

[TraceWilliams]

Isskint,

Thanks for the reply...

This is the module I built and I keep getting a User Type error when declaring "Dim XLapp As Excel.Application"

Function PWWbk()

Dim WbkName As String
Dim WbkPW As String
On Error GoTo Err_fGGO
Set rst = CurrentDb.OpenRecordset("FilesTBL")
Do While Not rst.EOF
If rst![FileName] Is Null Then
MsgBox ("No file name presented. Application will close.")
Exit Do
End If
Dim XLapp As Excel.Application
Set XLapp = GetObject(, "Excel.Application")
Set WbkPW = "Testing12"
Set WbkName = "S:\" + rst![FileName]

XLapp.Workbooks.Open (WbkName)
WbkName.Password = WbkPW
WbkName.Save
WbkName.Close
rst.MoveNext
Loop
Err_fGGO:
MsgBox ("This is not working! Application will close.")
Exit Function
End Function

Any ideas?

 

[Isskint]

Have you set a reference to the relevant Excel library in your access database?
From the Visual Basic Editor (ALT + F11 from your database) Tools>>References>> find Microsoft Excell x.x Object Library x.x is the version of Excel you have installed.

 

[TraceWilliams]

I did but I still cannot get the loop to work....

I decided to try this loop in Excel 2007 module:

Sub PWProtect()
Dim NowWrBk As Workbook
Dim WbkName As String
Dim WbkPW As String
Dim i As Integer
Application.ScreenUpdating = False
Set NowWrBk = ActiveWorkbook
LastRowA1 = NowWrBk.Sheets("List").[A1].End(xlDown).Row
For i = 1 To LastRowA1
WbkName = "S:\" + NowWrBk.Sheets("List").Cells(i, 1).Value

On Error Resume Next
Workbooks.Open WbkName, UpdateLinks:=False
ActiveWorkbook.Protect Password:="Testing12", Structure:=True
ActiveWorkbook.Close SaveChanges:=True
On Error GoTo 0
Next i
Application.ScreenUpdating = True
End Sub

This module runs but the password does not get set. Any ideas?

 

[Isskint]

i have been playing with this and the only problem i encountered was calling an xlsx (normal spreadsheet) workbook when it should have been an xlsm (macro enabled spreadsheet).

The first thing i would suggest is to remove the "On Error Resume Next" so you can see if there are any errors in the script or variables being passed.

 

[philipkdonkor]

Please note, I formatted my hard-drive but before formatting the drive, i copy the database folder from the hard-drive.

After formatting the drive, i put the folder back on the hard-drive but when i try to open the file, it give an error message.

What can i do to resolve this problem?

 

[philipkdonkor]

Please note, I formatted my hard-drive but before formatting the drive, i copy the database folder from the hard-drive.

After formatting the drive, i put the folder back on the hard-drive but when i try to open the file, it give an error message.

What can i do to resolve this problem?