Hi, using Excel 2000.

I want to ensure that a file with the code isn't saved in error or during
testing. The code below saves the active workbook which works the majority
of times but has on occasion, saved the file with the code. I have set up a
line of code to capture the active file name when the code is run but can't
get the code that saves the file to display a message if the file bring
saved is the one with the code and then closes the file without saving.

Thanks in advance of any help. Rob

' this line sets iFname to the active file name
iFname = Application.ActiveWorkbook.Name

'save the active file
Set Wb = ActiveWorkbook
Application.DisplayAlerts = False
Wb.Save
Application.DisplayAlerts = True

ActiveWorkBook is ver dangerous code as it depends on what is active at the
time. The workbook running the code is ThisWorkbook. You do not indicate how
the other workbook is created or accessed so it is hard to comment. In
general you want to create a reference to the new workbook as soon as it is
created or accessed.

As a quick and dirty you could check the name of WB against the name of
thisworkbook

this line sets iFname to the active file name
iFname = Application.ActiveWorkbook.Name

'save the active file
Set Wb = ActiveWorkbook
Application.DisplayAlerts = False
if Wb.name <> Thisworkbook.name then
Wb.Save
else
msgbox "Wrong file to save"
end if
Application.DisplayAlerts = True
--
HTH...

Jim Thomlinson


"Rob" wrote:

> Hi, using Excel 2000.
>
> I want to ensure that a file with the code isn't saved in error or during
> testing. The code below saves the active workbook which works the majority
> of times but has on occasion, saved the file with the code. I have set up a
> line of code to capture the active file name when the code is run but can't
> get the code that saves the file to display a message if the file bring
> saved is the one with the code and then closes the file without saving.
>
> Thanks in advance of any help. Rob
>
> ' this line sets iFname to the active file name
> iFname = Application.ActiveWorkbook.Name
>
> 'save the active file
> Set Wb = ActiveWorkbook
> Application.DisplayAlerts = False
> Wb.Save
> Application.DisplayAlerts = True
>
>
>

Thank you Jim, maybe quick and dirty but does the job really well.

Regards, Rob
"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:BA04D8E6-6369-4555-B48F-(E-Mail Removed)...
> ActiveWorkBook is ver dangerous code as it depends on what is active at
> the
> time. The workbook running the code is ThisWorkbook. You do not indicate
> how
> the other workbook is created or accessed so it is hard to comment. In
> general you want to create a reference to the new workbook as soon as it
> is
> created or accessed.
>
> As a quick and dirty you could check the name of WB against the name of
> thisworkbook
>
> this line sets iFname to the active file name
> iFname = Application.ActiveWorkbook.Name
>
> 'save the active file
> Set Wb = ActiveWorkbook
> Application.DisplayAlerts = False
> if Wb.name <> Thisworkbook.name then
> Wb.Save
> else
> msgbox "Wrong file to save"
> end if
> Application.DisplayAlerts = True
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Rob" wrote:
>
>> Hi, using Excel 2000.
>>
>> I want to ensure that a file with the code isn't saved in error or during
>> testing. The code below saves the active workbook which works the
>> majority
>> of times but has on occasion, saved the file with the code. I have set
>> up a
>> line of code to capture the active file name when the code is run but
>> can't
>> get the code that saves the file to display a message if the file bring
>> saved is the one with the code and then closes the file without saving.
>>
>> Thanks in advance of any help. Rob
>>
>> ' this line sets iFname to the active file name
>> iFname = Application.ActiveWorkbook.Name
>>
>> 'save the active file
>> Set Wb = ActiveWorkbook
>> Application.DisplayAlerts = False
>> Wb.Save
>> Application.DisplayAlerts = True
>>
>>
>>