Password Protect the XL file using VBA on MS Access

[imfarhan]

Hi All,
I have an application on MsAccess2003, generate all sort of reports on Excel
I've got one of the code to change the excel file to read only using the following code its working file

     Case "XLS"
                   bEvent = CreateXLSReport(RunningTask)
                   If Len(rs.Fields("UserDirectory").Value) > 0 Then
                    'Assign Source and Dsticnation Filename to the vairables
                    strSourceFile = "" & RunningTask.DestDir & RunningTask.DestinationFilename & ""
                    strDestFile = "" & rs.Fields("UserDirectory").Value & "\" & RunningTask.DestinationFilename & ""
                    ' Set the values into FS copy variable
                     Set fs = CreateObject("Scripting.FileSystemObject")
                        'fs.CopyFile """" & RunningTask.DestDir & "\" & RunningTask.DestinationFilename & """", """" & rs.Fields("UserDirectory").Value & "\" & RunningTask.DestinationFilename & """"
                     fs.CopyFile strSourceFile, strDestFile
                         If rs.Fields("ReadStatus").Value <> 0 Then
                           
                           SetAttr strDestFile, vbReadOnly 'Change the output file to Readonly
                           
                           
                         End If
                     
                     Set fs = Nothing
                     
                   End If

Problem , Now I want to make it Password protected , I 've tried following code but not much help.

1st Try:

ActiveWorkbook(strDestFile).ChangeFileAccess WritePassword:="admin" '14/08/2012

Generate error
?err.description
Object variable or With block variable not set

2nd Try

                     fs.CopyFile strSourceFile, strDestFile
                         If rs.Fields("ReadStatus").Value <> 0 Then
                           
                           'SetAttr strDestFile, vbReadOnly
                           
                           'Set wbf = Workbooks.Open(strDestFile)
                           Set wbf = Workbooks(strDestFile)
                            'wbf.WritePassword = "admin" '14/08/2012
                           
                            ActiveWorkbook(strDestFile).ChangeFileAccess WritePassword:="admin" '14/08/2012
                            'FileFormat:=xlNormal, Password:="xxxxx", WriteResPassword:="", _
                            'ReadOnlyRecommended:=False, CreateBackup:=False
                           
                           wbf.Save
                           wbf.Close

The above code it bit may confuse you , so I've tried two ways one with "ActiveWorkbook" but it generate error :"Object variable or With block variable not set" so I've declare Object/Workbook variable "wbf" but some time its work but the file not password protected and some time its genearte the error:
Error = "Subscript out of range”

I hope make sense

Many thanks for your help in advance
Kind regards
Farhan

 

[Kreszch68]

try the Protect method. This applies to a workbook and a worksheet as well.

 

[imfarhan]

try the Protect method. This applies to a workbook and a worksheet as well.

       Set wbf = Workbooks.Open(strDestFile)
       wbf.Protect Password:="admin"    '14/08/2012

Do you think the problem , the value on the variable "strDestFile" keep "Path" and "File_Name" together
.... sorry bit lost my self i think, i'm making some fundamental mistake.
coz After Open(strDestFile) the ActiveWorkbook should work isn't it?

Regards
F

 

[Kreszch68]

After opening the workbook, it isn’t necessarily the Activeworkbook.</SPAN></SPAN>
You’ll need to activate it, wbf.Activate</SPAN></SPAN>
But why use ActiveWorkbook as Wbf is the object you want to protect? </SPAN></SPAN>

 

[imfarhan]

Still No Success?

1st Try

' wbf.SaveAs Filename:=strDestFile, FileFormat:=xlNormal, _
' Password:="z", WriteResPassword:="z", ReadOnlyRecommended:=True, _
' CreateBackup:=False

2nd Try

                         wbf.Activate
                                  wbf.WritePassword = "z"

I have also tried wbf.Password its run ok with no error but when I open the file doesn't not ask the password

I appreciate your help Kreszch68

Regards

 

[imfarhan]

Hurray!!!!
The mistake I was looking the wrong file, my application generate two copied one master whic his not password and one for users which should be password so when I looked the actual variable values (strDestFile) it was doing ok so to make simple I've used the following codes:
Thanks for your help
Kind regards
Farhan

Sub UsePassword()

Dim wkbOne As Workbook

Set wkbOne = Application.Workbooks.Open("C:\Password.xls")

wkbOne.Password</STRONG> = InputBox ("Enter Password")
wkbOne.Close

End Sub
</STRONG>