BeforeClose and Workbooks.SaveAs Filename:=

[MeistersingerVonNurnberg]

Hi All -

I've got a function call to save a workbook in the ThisWorkbook BeforeClose.
The function contains the following line:

Workbooks(sWbName).SaveAs Filename:=var_FileName

where, sWbName is a String, which is passed to the function as
ThisWorkbook.Name

and, var_FileName is a local Variant that is set as follows:
var_FileName = Application.GetSaveAsFilename(not showing args)


For whatever reason, the SaveAs line is not working. It raises no Err. Through
the debugger, sWbName is set as the currently opened workbook, and is correct.
Also through the debugger, var_FileName gets set properly by
Application.GetSaveAsFilename(), and contains the full path to the file.

Any ideas? Thanks.

 

[Joel]

1) Are you actually getting to the line of code. Put a break point (F9) on
the SAVEAS statement and make sure you are getting to the line
2) How do you know that the file is not getting saved/changed? try
refreshing the window browser you are using to make sure the file isn't
updated or try saving the file under a different name
3) Maybe you workbook hasn't changed since the last time you saved the file.
Make a changed to the worksbook and try running the code again.

 

[JLGWhiz]

Seems very odd. Try putting a break point on the line after your SaveAs
line. Then
use the tool tip display to check the values of the two variables for
workbook name after it has executed and before the macro ends. If both are
correct at that point, and it still does not save or change the window
caption at the top of the screen, then there is a real problem.

 

[MeistersingerVonNurnberg]

Hi Joel - Thanks for helping.

1. Yes, I put a beak on the line; highlighted dark red. I get to the line. I
can see the appropriate values in the tooltip. I "Add Watch" - the values are
there. Everything cast properly. The length of the SAVEAS filename is 84.
2. I keep entering a file in the browse under the path the dialog shows. The
file isn't there that for sure. Did a drive search - the file isn't there.
3. Shouldn't matter if it hasn't changed since I am SAVEAS - right? The WB
is set as readonly from the Windows attributes perspective. The point w this
wb is that it shouldn't change which is why I am prompting to save.

This is actually a common proc - this WB save function. It's been alright at
least since last year at this time. Running XL 2002 from Office Pro 2002 and
XP SP3 and IE 7.05xxx

 

[Joel]

The only thing I can think of that may be the cause of the problem is if the
file was being save with the wrong extension. If the extension was a
reserved extension for system files and on the window explorer you have the
option to hide system files then you wouldn't find the file even though it is
there. Make sure you have the XLS extension on the end of the filename.

 

[MeistersingerVonNurnberg]

Hi -

I performed everything as you stated. The values are correct.

Maybe it has something to do w the wb being set as readonly from a Windows /
DOS perspective...

Or, perhaps ... In the BeforeClose, all the way at the end,
ThisWorkbook.Saved = True has something to do w it

 

[MeistersingerVonNurnberg]

Hi Joel -

Thanks again pal. Anyways, I changed the readonly attribute under Windows,
and the file saves over itself no problem when its changed. That happens
through an adjacent branch of logic to the SAVEAS, but is in the same public
function

file extension is clean; I am showing all sys/hidden files and folders in
Explorer as a typical personal approach... nothing there...

I know windows likes to hide things (like recycler files). I have procs that
look for that kind of stuff - the file isn't there... Further, when I hit the
line of code in the debugger, I don't see any hit to the drive... for a 2mb
wb i should get a little burst - nothing.

any ideas may help...

 

[JLGWhiz]

I just read the back and forth between you and Joel. There is nothing wrong
with the syntax that you are using. If the workbook is not protected, it
should save to the new name without a problem. The line of code that you
mentioned later in the BeforeSave sub should not matter since it would not
have executed at that point. You mentioned that the workbood is read only.
Does that mean Protected? If so, remove the protection and see if it will
execute the SaveAs then.

 

[JLGWhiz]

I have tried to duplicate the condition with different protection settings
and cannot get it to perform as you described. In fact, it does saveas a new
file name with the workbook protected, so that was not the cause. I hope you
will post back if you do find the culprit.

 

[MeistersingerVonNurnberg]

Hey again -

My problem may be where I allow a user to exit the wb in question. Under
File-Close (the wb) or File-Exit (xl altogether) - no problem - the save as
works as expected. Its when I trigger the close using ThisWorkbook.Close from
one of the available custom TB functions. The event fires for sure, I see
that, and ultimately, the wb closes. It just won't save as the file.


Maybe it has something to do w my understanding of how the events are
working. Help me if u can.


here is a public sub in its own module:

Public Sub Close_FromMe()
ThisWorkbook.Close SaveChanges:=False
End Sub


and here is ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Workbooks.Add
ThisWorkbook.Saved = True
End
End Sub


I would have thought that the close in the public sub would have triggered
the BeforeClose wb event, either at the wb or app level, and I would get a
new wb, presumably "Book1" and wb would close. It does not do that. It just
closes the wb, and leaves XL open w no wb.

If I File-Close, or "X" out of the wb, no problem, there is a new "Book1"
wb. And no code running (I presume bc its closed).


thanks a bunch pal, and ttul

 

[JLGWhiz]

From what I see, you do not need the BeforeClose event Since you are not
saving the changes to your workbook, you could simply use the SaveAs and
create the new workbook. The old workbook is no longer open, but is still in
the disc file. You basically change the name of the open workbook this way.
Try it with a file and see if it does what you want. Just open a file, do a
saveas with a different name. You will see the new name on the window
caption and only one workbook open.
The old workbook will still appear in the folder.

You only need the BeforeSave event to do special tasks related to workbook
content, such as validating certain data fields, hiding rows, columns or
sheets, etc.
You don't need it for a SaveAs when the changes to the old book are not to
be saved.