Alter Mulitple Protected VB Modules

[biosid]

We have an engineer here who created a highly useful
workbook with VB Modules. He password protected
the VBA Project.

Unfortunately, he hard-coded a network path in these
modules, and that path must change for reasons beyond
our control.

The problem is that this workbook has been copied literally
thousands of times. All of them need to be fixed.

They are all on mapped network drives that I can access,
so I know where all the copies are. I've been looking for
some way to automate a find / replace on all these workbooks,
but I see there is no good way to use VBA to unprotect
the modules.

I'm not restricted to VB or VBA. I could do something in
VC++ or Perl or whatever.

Any suggestions on how to do the fix? Right now, he's
facing the possibility of having to manually open and
unprotect thousands of workbooks.

 

[Zone]

Bio,
I always feel inferior to the large brains in here, but I did go to
the trouble to place my path and file specs in cells on a separate
worksheet in my main file. That might be worth a look to avoid this
problem in the future. I lost the VBA password to my main file and
eventually had to order a VBA password cracker from Passware
(lostpassword.com). At $45, I though it was a bit steep, but it surely
did the job (in about 2 seconds). They might be able to work with you
if you contact them and explain the problem. I think they had a
'contact us' link on the website, but if not, their phone is
952-646-5526. They certainly don't give away their stuff (there is a
free version to crack passwords of two characters or less), but it
might be worth it if you total up the enginner's time to manually enter
all those passwords. With the breadth of programming you have at your
disposal, you might be able to come up with a way to open the files,
crack the pw with their stuff, and change the path. Well, a
suggestion. James

 

[biosid]

I am hoping to take this time to alter the thing so it's immune to
future path changes. Putting this on one of the sheets is good
idea. You CAN unlock those via code if you need to.

 

[Jim Thomlinson]

If I understand correctly our issue is not one of not knowing the password,
it is how to modfy the existing code which is password protected. The short
answer is No. There is no relyable method of unlocking a password protected
project via code. You can use send keys but... No guarantees...

 

[biosid]

If I understand correctly our issue is not one of not knowing the password,
it is how to modfy the existing code which is password protected. The short
answer is No. There is no relyable method of unlocking a password protected
project via code. You can use send keys but... No guarantees...

I wouldn't want to rely on SendKeys with this many files. I've had
bad luck with that sort of thing.

What about a C program to open the workbooks as binary files,
and make the alteration?

 

[Jim Thomlinson]

Open up one of the Excel files with a hex editor and take a look at it. It
might give you some insight into how you might (emphasis on might) be able to
do this. Most of the file will look like typical binary file with the
exception of a few non-tokenized tid bits of joy and happyness. If the path
was stored as a constant then you might (emphasis on might) be in luck. That
being said you could also corrupt the files... Before you run any C script on
the files make darn sure you have a good set of backups.

 

[Zone]

Pardon, I meant the Search this Group feature. James

 

[Peter T]

In light testing a hard coded string appears to remain intact in the file,
even in a pw protected and compiled wb.

Have a look at the this set of routines.

http://tinyurl.com/j9dke

In hasCode() change

ba() = "_VBA_PROJECT_CUR"

to
ba() = "your-path"

and delete the 'Step 2' here
For j = 0 To UBound(ba) Step 2

in Sub Test()
change

sFolder = Application.DefaultFilePath & "\"

to
sFolder = "path-with-a-lot-of-files" ' can adapt later to do subfolders

So the first question is does this successfully find the hard coded string
in all anticipated files. If yes it would be simple enough to replace and
'print' back to file. But I don't know if changing the length of the string,
and hence the length of the file will throw various internal pointers. I
haven't tested, but maybe you can and let us know.

Regards,
Peter T

 

[Zone]

Bio, apparently my last reply was posted the wrong message. Please
ignore. Pardon me. James

 

[biosid]

So the first question is does this successfully find the hard coded string
in all anticipated files. If yes it would be simple enough to replace and
'print' back to file. But I don't know if changing the length of the string,
and hence the length of the file will throw various internal pointers. I
haven't tested, but maybe you can and let us know.

Regards,
Peter T

I tried this. If the "replace" string is exactly the same length
as the "find" string, then all is OK. If the lengths are different,
Excel reports the file is corrupted and cannot be repaired.

Of course, in my case the lengths would not be the same.
I'm using a hex editor to examine the files. Maybe the
string length is encoded somewhere nearby. I'll let you
all know if I find such a thing.

 

[biosid]

I tried this. If the "replace" string is exactly the same length

as the "find" string, then all is OK. If the lengths are different,
Excel reports the file is corrupted and cannot be repaired.

Of course, in my case the lengths would not be the same.
I'm using a hex editor to examine the files. Maybe the
string length is encoded somewhere nearby. I'll let you
all know if I find such a thing.

I forgot to mention, I also tried this using a Perl command
line statement...

perl -pe 's/oldpath/newpath/g' BrokenFile.xls

It appears to work just as well. If the two strings are of
identical length, you're set. If not, you're screwed.

We're still screwed.

 

[Peter T]

Yes it's a killer!

No doubt there is a way of updating the relevant pointers but I haven't seen
any BIFF8 documentation how to do that for code in a file.

I would explore further use of SendKeys to unlock the project. I imagine you
have found code to do that, there are examples in this ng by Bill Manville
and others. The main issue of course is the reliability of SendKeys.
Following should at least ensure the password dialog is open and is the
front window.

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClasssName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function SetForegroundWindow Lib "user32" _
(ByVal hwnd As Long) As Long

Function GetDialog() As Long
'http://tinyurl.com/mohqz
Dim dlgWin&
Const dlgTITLE As String = "VBAProject Password"

dlgWin = FindWindowA(vbNullString, dlgTITLE)

If dlgWin Then
SetForegroundWindow dlgWin
End If

GetDialog = dlgWin

End Function

Run this just after the dialog 'should' have opened and before sending the
password. If GetDialog fails would need to figure something else, or abort &
document. Assuming got to the point of successfully 'sending' the password
might be worth checking following returns 0

dlgWin = FindWindowA(vbNullString, "VBAProject - Project Properties")

If dlgWin <> 0 then use an API to close, or ensure it's in front and
sendkeys "{ESC}"

Providing the relevant code is in identical module(s) (not sheet/workbook
modules) probably simplest to replace the entire module(s). You could adapt
this example -

'http://tinyurl.com/mohqz

But code can also be found & replaced in situ.

If possible I think better to open files in an automated session of Excel
with its Events disabled, to avoid Open events & automacros.

I haven't put all this together but relatively optimistic it should be
possible to batch process files. Obviously would take longer than replacing
the string in a closed files but at least could sit back and have a cup of
coffee!

Regards,
Peter T

 

[Peter T]

I forgot to add - if going to all this trouble why not avoid having to do
same in future. Don't replace a hard code the string but place in a hidden
cell or hidden defined name. Include new code in replaced module(s) to
retrieve & update. Or if viable code to retrieve the string from elsewhere.

Regards,
Peter T