SaveAs without Dialog

Question

Hi!

I've looked everywhere for the answer to this, but I couldn't find it.

I want several updates without the need of a user's confirmation.

This is what I got so far, but I always get the SaveAs dialog:

' ##### SAVE MACRO ENABLED TEMPLATE #####

Sub SaveTemplate()

     Dim fname As Variant, DateTime As String, myInitialFilename As String

     On Error GoTo ErrorHandler

Application.DisplayAlerts = False

         myInitialFilename = "C:\Users\Ultra\AppData\Roaming\Microsoft\Templates\Inventory.xltm" 

         'Get filename (with path) for saving

         fname = Application.GetSaveAsFilename(InitialFileName:=myInitialFilename & DateTime, fileFilter:="Plantilla de Excel Habilitada para Macros (*.xltm),*.xltm")

         If fname = False Then Exit Sub  'Exit if user hit Cancel

         Application.EnableEvents = False  'Prevent this event from firing

         ThisWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLTemplateMacroEnabled

           '52 = xlOpenXMLWorkbookMacroEnabled = xlsm

         Application.EnableEvents = True

Application.DisplayAlerts = True

 Exit Sub

     Application.EnableEvents = True

ErrorHandler:

     MsgBox "An error occured during saving process." & Err.Number, vbCritical, "Error"

 End Sub

As you can see, I used Application.EnableEvents, but it didn't prevent the dialog from openning. What can I do to solve this problem?

Also if possible, I'd like to know if there's a VBA code to find the path from C: to the folder containing Excel Templates in any Windows OS. Ex:

In my PC with Vista is: "C:\Users\Ultra\AppData\Roaming\Microsoft\Templates\Inventory.xltm" 

I want this process to be automatic

Thank you in advance.

Answer 1

You might want to tweak that just a bit to make sure they get put into the proper templates location.  This should work for that since .TemplatesPath returns the full path including the last needed \ separator:

Application.DisplayAlerts = False

ThisWorkbook.SaveAs Filename:= Application.TemplatesPath & _

 "Plantilla de Excel Habilitada para Macros.xltm" _

  FileFormat:=xlOpenXMLTemplateMacroEnabled

Application.DisplayAlerts = True

===

You mentioned earlier about going to the templates when the user clicks the Open button.  You can't really intercept that operation, but you can direct it to the initial directory you want with a little VBA trickery:

First, in a regular code module set up a Public variable to hold the path that the file itself was opened from; maybe something like

Public myHomePath As String

That has to go into a regular code module ahead of any Sub or Function declarations.  By being public, it can be referenced in any module in the VBA Project

Now, in your Workbook_Open() event process you could do something like this:

Private Sub Workbook_Open()

  'the .Path property does not have "" at the end, so we

  'will add one (and by using .PathSeparator should work with Macs also)

  myHomePath = ThisWorkbook.Path & Application.PathSeparator

  'the .TemplatesPath property does include the final "" separator.

  ChDir Application.TemplatesPath

End Sub

Because you've done the ChDir, when you click the File --> Open button, it will start looking in the Templates folder.

If you ever need to "go home" to the folder you started in, you can now use

ChDir myHomePath

to get the default path pointed back to that location.

Answer 2

Aha! - that fine point I was saying I must be overlooking ... I was looking too hard at the .SaveAs and not hard enough at the .GetSaveAsFilename.  I figured he just couldn't be talking about that one.    And even as I looked at that, it seemed rather specific to me.

excelBert - you don't need to Get a filename if you already have one in mind, your .SaveAs statement might possibly be written this way:

Application.DisplayAlerts = False

ThisWorkbook.SaveAs Filename:="Plantilla de Excel Habilitada para Macros.xltm" _

 FileFormat:=xlOpenXMLTemplateMacroEnabled

Application.DisplayAlerts = True

Although that may not be what you had in mind - you would not need the .GetSaveAsFilename at all.

Answer 3

1. are you getting twice the "saveas" dialog ?

one shows-up for this:

fname = Application.GetSaveAsFilename(InitialFileName:=.....

BTW what is: => InitialFileName:=myInitialFilename & DateTime

2. to find-out that folder, try: => application.templatespath

hth,

hector.

Answer 4

Bert wrote:

I've looked everywhere for the answer to this, but I couldn't find it.  I want several updates without the need of a user's confirmation.  This is what I got so far, but I always get the SaveAs dialog:

[....]

Application.DisplayAlerts = False

         myInitialFilename = _

         "C:\Users\Ultra\AppData\Roaming\Microsoft\Templates\Inventory.xltm" 

         'Get filename (with path) for saving

         fname = Application.GetSaveAsFilename(InitialFileName:=myInitialFilename & DateTime, _

             fileFilter:="Plantilla de Excel Habilitada para Macros (*.xltm),*.xltm")

[....]

         ThisWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLTemplateMacroEnabled

I'm at a loss to understand the problem.  Or perhaps you are. ;-)

You get a SaveAs dialog because you request one.  That is what Application.GetSaveAsFilename does.

If you do not want that SaveAs dialog, you do not want to give the user the opportunity to change the file name.  So perhaps you want simply:

fname = myInitialFilename & DateTime

Note:  I do not see where you set DateTime in your code excerpt.  Lost in translation?

Because you set DisplayAlerts=False, you do not get a SaveAs dialog or even a "do you want replace it?" dialog when you execute ThisWorkbook.SaveAs, as you wish.

PS:  I think

     Application.EnableEvents = True

ErrorHandler:

should be

ErrorHandler:

Application.DisplayAlerts = True

    Application.EnableEvents = True

Answer 5

I actually don't see any reason it shouldn't save without the dialog.  But I might be missing some fine point here.

Although you mentioned you used Application.EnableEvents, that really doesn't have much to do with the issue.  However, the Application.DisplayAlerts setting do.  The only suggestion I have at this time is to move the Application.DisplayAlerts statements in tighter to the .SaveAs operation - like

Application.EnableEvents = False 'Prevent this event from firing - really not needed

Application.DisplayAlerts = False

ThisWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLTemplateMacroEnabled

'52 = xlOpenXMLWorkbookMacroEnabled = xlsm

Application.DisplayAlerts = True

Application.EnableEvents = True ' not needed except because set to =False earlier