Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

Avoid saving file with code in

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rob - 13 Mar 2008 21:39 GMT
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
Jim Thomlinson - 13 Mar 2008 23:03 GMT
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
Signature

HTH...

Jim Thomlinson

> Hi, using Excel 2000.
>
[quoted text clipped - 15 lines]
>         Wb.Save
>         Application.DisplayAlerts = True
Rob - 14 Mar 2008 21:42 GMT
Thank you Jim,  maybe quick and dirty but does the job really well.

Regards, Rob
> ActiveWorkBook is ver dangerous code as it depends on what is active at
> the
[quoted text clipped - 43 lines]
>>         Wb.Save
>>         Application.DisplayAlerts = True
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.