Saving Protected Workbooks

[Guest]

I have a problem I hope someone can help.

I have a workbook with a VBA project, both of which are password protected.
I want to progmatically save the changes to the workbook.

I can unprotect, change necessary cells, and then reprotect the workbook.
However I get an error message saying that the file cannot be saved when the
VBA project is protected. I cannot find any information as to how to
unprotect the VBA during saving and then reprotect.

Or am I going about this the wrong way.

 

[Duncan]

I have had projects that would save with the VBA protected, maybe its
something else in your code, like perhaps trying to save while it is
running something in VBA?

Not sure, but it would help anyone reading this if you posted your code
that performed the actions, and show where it bugs out

Regards

Duncan

 

[Guest]

Duncan
Here is some of the code:-

Code Start******************************

Option Explicit

Public Function chkDataSource() As Boolean
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FileExists(Sheets("Benchmark").Range("DataLocation")) Then
getDataSource
End If
Set fs = Nothing
End Function

Private Sub getDataSource()
Dim strFullPath As String
Dim strXLDirectory As String
Dim strSavedFileName As String
Dim bwrongext As Boolean
On Error GoTo Err_getDataSource
reenter:

If Not getFileName(strFullPath, LOCATE_DATA_TITLE, True, strXLDirectory, _
ACCESS_FILTER, ACCESS_EXTENSION, strSavedFileName,
bwrongext) Then
' The User cancelled, a file was not chosen.
Exit Sub
Else
' Check that the chosen file is our database file
If StrComp(strSavedFileName, "Benchmarker.mdb", vbBinaryCompare) <>
0 Then
If MsgBox("The file you chose is not the correct Database File."
& vbCrLf & _
"Please locate the file 'Benchmarker.mdb'." & vbCrLf &
vbCrLf & _
"Do wish to continue ?", vbCritical + vbYesNo, "Can't
Continue") = vbNo Then
Exit Sub
Else
' Try again
strFullPath = ""
strXLDirectory = ""
strSavedFileName = ""
bwrongext = False
GoTo reenter
End If
If bwrongext Then
MsgBox "The file you chose is not a saved" & vbCrLf & _
"Access Database file." & vbCrLf & vbCrLf & _
"The Data Source could not be Opened", vbExclamation,
"Bad file type"
Exit Sub
End If
End If
End If
MsgBox "The Benchmarker will now be saved to update" & vbCrLf _
& "the new database location.", vbInformation, "Saving Workbook"
ActiveSheet.Unprotect Password:=PWD
ActiveSheet.Range("DataLocation") = strDBName
ActiveSheet.Protect Password:=PWD, DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveWorkbook.Save

Exit_getDataSource:
Exit Sub
Err_getDataSource:
lngErr = Err.Number
strErr = Err.Description
Err.Clear
CentralError "Module basDataSource", "getDataSource() ", strErr, lngErr
Resume Exit_getDataSource

End Sub

Code End ***************
The code bombs out at 'ActiveWorkbook.Save'

 

[Duncan]

Sentinel,

I have looked at the code, but unfortunately too inexperianced to see
any problems.

Hopefully someone will spot something and post back to you, I will keep
looking in the meantime as im sure it is staring us in the face.

As for unprotecting the vba, the only way this is possible is by using
sendkeys (very unsafe) as by default it is not allowed through code to
prevent people mashing through in their attempts to break in.

Any example of using sendkeys that I have found is not reliable and you
would really need to change to suit your app.

Sub TestUnlock()
'MsgBox "opening vbe"
SendKeys "%{F11}", True
'MsgBox "going to project explorer"
SendKeys "^R", True
'MsgBox "pageup 5 times"
SendKeys "{PGUP 5}", True
'MsgBox "hitting enter"
SendKeys "{ENTER}", True
'MsgBox "entering password"
SendKeys "XXXXXXXXXX", True
'MsgBox "hitting enter"
SendKeys "{ENTER}", True
End Sub

try changing that to see if you can get it to work, then your problem
might be solved, but I warn again, sendkeys is not safe or reliable in
the slightest. We really need to find out what is going wrong in your
code as I cannot see it.

Sorry I couldnt be of help.

Duncan