Command Button Save As Application.ExecuteExcel4Macro ("SAVE.AS?()

P

Paul Dennis

Is it possible to change this code to show which directory the SAVE.AS should
use asthe default?
 
M

Mallasch

Tools --> Options --> General Tab --> Default file location:

The above is the menu option to change the default location. You have to
exit MS Excel then reopen it.

I don't see any of the code so if it is some VB code then probably use:

Sub DefaultLocation()
Dim strSaveAs As String
strSaveAs = Application.DefaultFilePath
MsgBox strSaveAs
End Sub
 
P

Paul Dennis

I don't want to change the dafault location for all xls files, just the one
with this button, i.e. they press the button and it shows the save as pop up
dafaulting to the directory of my choice, but just for this one xls.

The code I'm using is

Private Sub SaveForm_Click()
Application.ExecuteExcel4Macro ("SAVE.AS?()")
End Sub

and I want the default location to be "C:\Service Improvement\ToDo"
 
D

Dave Peterson

This kind of thing works for a mapped drive:

Option Explicit
Private Sub SaveForm_Click()

Dim CurFolder As String
Dim NewFolder As String
Dim TestStr As String

CurFolder = CurDir
NewFolder = "C:\Service Improvement\ToDo"

TestStr = ""
On Error Resume Next
TestStr = Dir(NewFolder & "\nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox "design error!"
Else
ChDrive NewFolder
ChDir NewFolder
End If

Application.Dialogs(xlDialogSaveAs).Show

ChDrive CurFolder
ChDir CurFolder

End Sub
 
P

Paul Dennis

thanks - I tried it but it didn't change anything, still pointing to the
current directory.
 
Top