SaveAs Method problem in macro

[Terence]

Hi,

I have 2 questions:

(1)
My Excel macro file contains 2 worksheets. "Sheet1" has
somes cells for users to enter criteria and 2 buttons.
The first button is to activate the action of data
retrival from SQL server. The retrived data is then put
in "Sheet2" of the same file.
After data is retrieved into "Sheet2", users can check the
data and do some editing if necessary.
There is another button on "Sheet1" which is for saving
the data in "Sheet2" into another file (C:\TESTING.XLS)
and the following code is used in the program:
'***************************************
Sheets("Sheet2").Select
Range("A1").Select
ActiveWorkbook.SaveAs FileName:="C:\TESTING.XLS", _
FileFormat:=xlExcel7, CreateBackup:=False
'***************************************

Yes, the data in "Sheet2" is saved in the file
C:\TESTING.XLS, but I have to set some workbook and Sheet
protection for my original file since I don't want the
users to change anything in my original file. However, the
program got an error when "SaveAs" is run since this
method also changes the filename of the original file to
TESTING.XLS
How to overcome this problem??

(2)
Actually I don't want to change the filename of my
original macro file when "SaveAs" method is run. It is
because I am afraid that the original file is saved to
TESTING.XLS accidentally by users when they close the
file. Is there any method better than "SaveAs" ?

Thanks a lot!

Terence

 

[Jan Karel Pieterse]

Hi,

Check out the SaveCopyAs method in Help, it sounds as if
that is what you need.

Furthermore, you can prevent your user saving your file by
using the BeforeSave event macro in the Thisworkbook
module and setting cancel to true:

In the Thisworkbook module:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
If Not bIamSavingMyself Then
MsgBox "Save not allowed!!!"
Cancel = True
End If
End Sub


In a normal module:

Option Explicit

Public bIamSavingMyself As Boolean

Sub SaveMyself()
bIamSavingMyself = True
'Now you can save!!
End Sub

Sub resetsave()
bIamSavingMyself = False
'Now you cannot save!!
End Sub



Regards,

Jan Karel Pieterse
Excel TA/MVP