PC Review


Reply
Thread Tools Rate Thread

ActiveWorkbook.SaveAs does not work

 
 
Cooz
Guest
Posts: n/a
 
      29th Nov 2007
Hi everyone,

I've created these two subs in an Excel template, which, in my humble
opinion, should prompt the user with a filename each time a worksheet based
on the template is saved. The template contains the Name "klant" which refers
to a single cell that currently contains the text "qqq". The code below is
stored in the ThisWorkbook section.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not ActiveWorkbook.Saved Then
ActiveWorkbook.Save
End If
If Not ActiveWorkbook.Saved Then ' Cancel is chosen in the SaveAs dialog
Cancel = True
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' This sub proposes a name each time a save occurs (ddMMyy hhnn <klant>.xls).
Dim strFileName As String, varResult As Variant

If Right(LCase(ActiveWorkbook.Name), 3) <> "xlt" Then ' leave .xlt alone
Cancel = True ' do not save by default
strFileName = Format(Date, "ddMMyy ") & Format(Time, "hhnn ") & _
Application.Range("klant").Value & ".xls"

varResult =
Application.GetSaveAsFilename(InitialFileName:=strFileName)
If varResult <> False Then
Application.EnableEvents = False ' or else another
Workbook_BeforeSave
ActiveWorkbook.SaveAs Filename:=CStr(varResult) ' DOES NOT WORK
Application.EnableEvents = True
End If
End If

End Sub

In stepping through the code I find that the line with
ActiveWorkbook.SaveAs... gets reached when it should and that a valid
filename is provided - however, nothing is saved. Can anyone confirm this
mystery and if yes - is there a remedy?

Thank you,
Cooz
 
Reply With Quote
 
 
 
Reply

« PageSetup | round »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ActiveWorkbook.SAVEAS for *.xls Faraz A. Qureshi Microsoft Excel Programming 6 4th Aug 2009 09:08 AM
saveas ActiveWorkbook.SaveAs Filename:=Range("A1").Value DarrenL Microsoft Excel Programming 4 18th Apr 2009 07:54 AM
Help with ActiveWorkbook.SaveAs Martin X. Microsoft Excel Programming 3 15th Aug 2007 02:20 PM
ActiveWorkbook SaveAs Mike Microsoft Excel Programming 2 11th Feb 2005 03:23 AM
RE: activeworkbook.saveas =?Utf-8?B?SiBTaWx2ZXI=?= Microsoft Excel Programming 0 25th Jun 2004 09:01 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:07 PM.