Protecting VBA (Excel) code

[Malc]

I have a model that uses VBA to save the file. The variables are
dimmed as appropriate and then I use this code to compile the save
filename:

DSNDirectory = "\\" & Range("appserver") & "\" & Range("appprogdir") &
"\utils\"
DSNFilename = Range("dsnfile")

Then I use
Application.DisplayAlerts = False
ChDir DSNDirectory
ActiveWorkbook.SaveAs FileName:=DSNDirectory + DSNFilename, _
FileFormat:=xlTextMSDOS, CreateBackup:=False
DisplayAlerts = True

and this saves the particular worksheet as a text file.
And this all works until I lock the project for viewing and protect it
with a password. Once that's done running the macro that contains the
code described above results in an error 1004 method 'SaveAs' of
object '_workbook' failed. I can't debug it because it's locked, and
if I unlock it the code works. So I can't work out what's wrong!
Does anyone have any ideas?
Thanks in advance

 

[Peter T]

Guessing, the problem has nothing to do with the project being locked but
more likely a file named DSNDirectory & DSNFilename (btw note & vs +)
already exisits. For testing, comment the line that disables alerts.

If that's the reason it'll be up you if you want to check if a such a file
already exists or maybe

on error resume next
Kill theFile
On error goto 0
or better still include an error handler
On error goto errH

'code
exit sub
errH:
msgbox err.number & vbcr & err.description
exit sub


Regards,
Peter T

 

[Dave Peterson]

First, you don't need the "chdir" line. You don't use it and even worse, it
won't work with UNC paths.

Second, I'd use & to concatenate the dsnDirectory and dsnfilename. But VBA is
very forgiving.

Third, I'd use "application.displayalerts = true"

The first and second items shouldn't cause any problems.

The third may--but I would think that it would cause the same problem no matter
if the workbook's project is protected or not.

I don't think the problem is in the code you posted.

 

[Malc]

Guys - thanks for your help, but having taken your advice I still have
the problem.
I took out the error handler because I wanted to see what the error
actually was. I had displayalerts set to false because the file I'm
creating might or might not exist and with it set like this I don't
get the overwrite? message.
I'm still mystified why code that works perfectly well as long as it
isn't protected stops working as soon as it is!
Anyway, as there's a suggestion that the problem isn't in my code,
maybe you'd like to see all of it. I should say that I'm not a
programmer and I've picked up what I know, so please don't laugh too
much at my efforts!
I also noticed that even with the project unlocked and the alerts
turned on I could overwrite the file OK but if I Cancelled or said No
I'd get the failure message.

Thanks again:

Dim DSNDirectory As String
Dim DSNFilename As String
Dim FileSaveDirectory As String
Dim FileSaveName As String
Dim PWEntry As String
Worksheets("Setup").Activate
Range("pwdentry").Select
If ActiveCell.Formula = "********" Then
Range("pwdentry").ClearContents
End If
Range("pwdentry").Select
If ActiveCell.Formula = "" Then
PWEntry = InputBox("Enter SunSystems Password")
Range("pwdentry") = PWEntry
'Exit Sub
End If
Range("pwdentry").Copy
Range("pwd").Select
ActiveSheet.Paste
Selection.NumberFormat = ";;;"
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Range("pwdentry").Select
ActiveCell.Formula = "********"
Range("appserver").Select

DSNDirectory = "\\" & Range("appserver") & "\" &
Range("appprogdir") & "\utils\"
DSNFilename = Range("dsnfile")

FileSaveDirectory = Range("FileSaveDirectory")
FileSaveName = Range("FileSavename")

Worksheets("DSN").Activate
Worksheets("DSN").Calculate

Application.DisplayAlerts = True
'Application.DisplayAlerts = False

****ERROR OCCURS HERE IF PROJECT IS LOCKED****
ActiveWorkbook.SaveAs Filename:=DSNDirectory & DSNFilename, _
FileFormat:=xlTextMSDOS, CreateBackup:=False
Application.DisplayAlerts = True

 

[Peter T]

Looks like you are trying to save the workbook with the locked code as a
textfile, indeed that fails for me too.

If you just want to save a sheet as a text file do something like this
(aircode)

activesheet.copy
activeworkbook.saveas ' blah
activeworkbook.close

In effect copy the sheet to a new single sheet workbook, saveas, then close
it.

In passing, try and remove all those Selects none of which are necessary if
you reference the ranges correctly.

Regards,
Peter T

 

[Malc]

Peter, you're a star. Thank you. It's so obvious now! And yes, I
will work on those selects - although in my defence I do find them
useful when debugging because I can see that I've gone to the right
cell...!!

Cheers

Malc