Protecting VBA (Excel) code
 

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

Protecting VBA (Excel) code
 

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

"Malc" wrote in message
...
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

Protecting VBA (Excel) code
 

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 wrote:

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

--

Dave Peterson

Protecting VBA (Excel) code
 

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

Protecting VBA (Excel) code
 

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" wrote in message
...
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

Protecting VBA (Excel) code
 

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




On 1 Feb, 17:01, "Peter T" <peter_t@discussions wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -