Excel file won't save to specified default folder - tries to save workbook to template folder.

Posted on 2014-08-08
Last Modified: 2014-08-17
Running Win7 Pro and Excel 2007.

I open a 'New' workbook by selecting a template.  Excel options is set to save to %User%\Documents\Excel.

I modify the file, then 'Save'.  Excel correctly tries to save it as a 'Macro-Enabled Workbook' (since that's how I set it,) but it tries to save the workbook to my %user%\appdata\roaming\microsoft\templates folder,

Why won't it save to the default folder I specify?

It there something in the macros I've created?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  'David McRitchie,  2007-09-07    insrtrow.htm on double-click
  '-- will copy more often than  Extend Formulas and Format (tools option)
  Cancel = True
  If Target.Row < 24 Then Exit Sub
  Sheet1.Unprotect Password:="white trash"
    Cells(Target.Row + 1, 1).EntireRow.Insert
  Cells(Target.Row + 1, 1).EntireRow.Select
  Application.CutCopyMode = False
  On Error Resume Next
  '-- customize range for what cells constants can be removed --
  Intersect(Selection, Range("a:IV")).SpecialCells(xlConstants).ClearContents
  ActiveCell.Offset(0, 2).Select
  Sheet1.Protect Password:="white trash", AllowFormattingCells:=True ' the default is False
  On Error GoTo 0
End Sub

And a module:

Sub DeleteMe()
    Dim Ret As Range

    On Error Resume Next
    Set Ret = Application.InputBox("Please select the Cells", "Delete Rows", Type:=8)
    On Error GoTo 0
    If Not Ret Is Nothing Then
        If Intersect(Ret, Rows("1:24")) Is Nothing Then
            ActiveSheet.Unprotect Password:="white trash"
            ActiveSheet.ProteExperts-Template---Clean.xlsmct Password:="white trash", AllowFormattingCells:=True ' the default is False
            MsgBox "Protected rows selected"
        End If
    End If
End Sub
Question by:eracer
    LVL 22

    Expert Comment

    Why won't it save to the default folder I specify?
    Go to Excel Options - Save. Do you have "%User%\Documents\Excel" set in the default location? If not, change it there and you should be good to go. As for your code, I don't see anything there prompting it to save. It would look something like this:

        ActiveWorkbook.SaveAs Filename:= _
            "C:\%User%\Documents\Excel\Experts-Template---Clean.xlsm", FileFormat _
            :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True


    Author Comment

    I have it set to a document directory, but when I open the workbook as a template, then 'Save As,' it ignores that setting and tries to save it to the 'Templates' folder.

    Accepted Solution

    For some reason it works properly.  I have no idea why, since I've changed nothing...

    Author Closing Comment

    It just started working as expected.  I have no idea why.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now