We help IT Professionals succeed at work.

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

581 Views
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"
  Target.EntireRow.Copy
    Cells(Target.Row + 1, 1).EntireRow.Insert
  Cells(Target.Row + 1, 1).EntireRow.Select
  ActiveSheet.Paste
  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"
            Ret.EntireRow.Delete
            ActiveSheet.ProteExperts-Template---Clean.xlsmct Password:="white trash", AllowFormattingCells:=True ' the default is False
        Else
            MsgBox "Protected rows selected"
        End If
    End If
End Sub
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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

Flyster

Author

Commented:
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.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
It just started working as expected.  I have no idea why.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.