Set directory prior to using the xlDialogSaveAs.show

[kengooch]

I have a spreadsheet that I build a file name from the date and the user and then need to store it to their network folder. I have tried numerous things but can't get Excel to change the current folder prior to executing the xlDialogSaveAs command. Here is where I am... any help would be appreciated.

'Build Staff File Save Name
    vTitle = "Accomplishment Tracking"
    vDate = Date - Weekday(Date, vbUseSystem) + 2
    vDate = Format(Date - Weekday(Date, vbUseSystem) + 2, "yyyymmdd")
    vWeek = Application.WorksheetFunction.WeekNum(Date)
    vLName = Left(Application.UserName, InStr(Application.UserName, " ") - 2)
    vFName = Mid(Application.UserName, InStr(Application.UserName, " ") + 1, 1)
'Set Variables for Filename
    vPath = "U:\MS-Tools\Accomplishment Tracking\"
    ChDir vPath
    vFileName = vDate & "-C Wk" & vWeek & " " & vFName & vLName & " Accomplishments" & ".xlsm"
'Save File
    Range("B3").Select
    fProcessing.Hide
jAgain:
    vChk = MsgBox("Welcome to the " & vTitle & " Workbook." & vbCrLf & vbCrLf & "This workbook provides daily worksheets to help you track your Daily Accomplishments." & vbCrLf + vbCrLf & "            T H I S   I S   T H E   M A S T E R   F O R M" & vbCrLf & "We need to create a working copy on your computer.", vbOKCancel + vbQuestion + vbApplicationModal, vTitle, 5000, 5000)
    If vChk = vbOK Then
        GoTo jSave
    Else
    If vChk = vbCancel Then
        MsgBox "        !  !  !  !     C A U T I O N      !  !  !  !" & vbCrLf + vbCrLf _
        & "You are working on the Master Document" & vbCrLf + vbCrLf _
        & "        any changes you make will affect" & vbCrLf _
        & "          every one using this document!", vbCritical + vbApplicationModal, "System Master Document Warning"
        GoTo jend
    End If
    End If
jSave:
    vUserDomain = Environ("UserDomain")
    vCompNm = Environ("ComputerName")
    vUsrNm = Environ("UserName")
jRetry:
    vAppUsrNm = InputBox("You are logged on as " & vUsrNm & vbCrLf & "Please provide your full name." & vbCrLf + vbCrLf & "Example Format ( Kennedy, John F. )", "Verify Application User Name", Application.UserName)
    If vAppUsrNm = "" Then
        MsgBox "You did not provide an entry. The Save As process will be canceled" & vbCrLf + vbCrLf & "! ! !  CAUTION: YOU ARE WORKING ON THE MASTER SHEET  ! ! !", vbOKOnly + vbCritical + vbApplicationModal, "Save As Process Aborted"
        vChk = vChk + 1
        GoTo jend
    End If
    Application.UserName = vAppUsrNm
 'Try to save to directory
        SetCurrentDirectoryA "U:\MS-Tools\Accomplishment Tracking\"
        Application.Dialogs(xlDialogSaveAs).Show vFileName
 'The line below works, but the user has no involvement
 '        ThisWorkbook.SaveAs Filename:=vFileName
 End Sub

Thanks in advance for any suggestions or help.

 

[Gasman]

Why not just combine the folder path and filename?

 

[kengooch]

I've done it both ways and it didn't work either way. Surely there is a way to do this.
Thanks for the input! Anyone else out there got an idea?

 

[KitaYama]

I've done it both ways and it didn't work either way. Surely there is a way to do this.
Thanks for the input! Anyone else out there got an idea?

In previous versions of Office we could use :

    TargetPath = "Z:\Test\
    ChDir TargetPath 
    Application.Dialogs(xlDialogSaveAs).Show

But later that stopped working for some reasons. We were obliged to change the drive first (just like cmd)

    TargetPath = "Z:\Test\
    ChDrive Left(TargetPath ,3)
    ChDir TargetPath 
    Application.Dialogs(xlDialogSaveAs).Show

And again after several updates, that stopped working too.

In Office 365 the following works for me

    TargetPath = "Z:\Test"
    ChDrive Left(TargetPath , 3)
    ChDir TargetPath 
    Application.GetSaveAsFilename

 

[KitaYama]

Forgot to say.
If you need to check the file was really saved or not you can use it this way

    Dim SavedFileName As String

    TargetPath = "Z:\Test"
    ChDrive Left(TargetPath, 3)
    ChDir TargetPath
    SavedFileName = Application.GetSaveAsFilename
   
    If SavedFileName = "False" Then
        MsgBox "Failed to save file"
    Else
        MsgBox "File saved to : " & SavedFileName
    End If

 

[kengooch]

Wow.. that worked in terms of setting the directory!! But it doesn't show the vFilename that is created. I looked up the syntax thinking I could add the name in, and I tried Application.GetSaveAsFilename (vFileName) but that didn't work either. What is the trick to show the created filename when the file dialogue opens?

 

[KitaYama]

If you need to set the default file name you can use this:

    FolderName = "Z:\Test"
    strFile = "test.xlsx"
           
    FullName = FolderName & "\" & strFile
    myFile = Application.GetSaveAsFilename(InitialFileName:=FullName, _
                FileFilter:="Excel Files (*.xlsx), *.xlsx", _
                Title:="Save Report to Directory")

 

[kengooch]

If you need to set the default file name you can use this:

    FolderName = "Z:\Test"
    strFile = "test.xlsx"
          
    FullName = FolderName & "\" & strFile
    myFile = Application.GetSaveAsFilename(InitialFileName:=FullName, _
                FileFilter:="Excel Files (*.xlsx), *.xlsx", _
                Title:="Save Report to Directory")

For some reason I am getting an error message. Should the .xlsx be .xlsm? since this is a macro enabled workbook?

 

[KitaYama]

For some reason I am getting an error message. Should the .xlsx be .xlsm? since this is a macro enabled workbook?

I don't have any problem with that code. You can save a xlsm file as xlsx and there will be no error at all.
But you may want to give it a try.

By the way Did you check for any type miss? what's the error message? Does the dialogue opens to the selected folder? Does the file saves at all?

 

[kengooch]

I don't have any problem with that code. You can save a xlsm file as xlsx and there will be no error at all.
But you may want to give it a try.

By the way Did you check for any type miss? what's the error message? Does the dialogue opens to the selected folder? Does the file saves at all?

It was an odd error, "process interrupted" or something like that... I can't seem to reproduce it now. But the SaveAs window pops open, the file name shows in the file box, but the file actually saved to the current system active drive, even though it showed it was going to save to the U:\ drive! Really odd.

 

[KitaYama]

@kengooch Unfortunately I don't have Excel installed and I can not test what's going on.
Here's some points you may want to consider:

• What's the purpose of preparing the file path & file name and to open save dialogue? The only thing that remains is someone (your user) must click a button. If you have prepared everything, you can save the file right away. I don't think you need the saveas dialogue.
  The following and it works in any version of Excel no matter what.
  

  FileName = "Z:\Test\test.xlsm"
  ActiveWorkbook.SaveAs FullName

  
  
• What's the purpose of saving the same macro enabled file in different location? Normally, you have a macro enabled workbook that behaves like a template. You do some operation, export the sheets to a new xlsx file and save the new workbook.
  If you really need the macro or functions to run on all your files (or a part of them), you must move your macro to personal.xlsx file. In this way every and all workbooks have access to the macro. (if you need guidance on this let me know)
  
  
• If for some reasons, you want to prepare the file path and file name and give the user the option to change it if necessary, check if the location is a trusted. If everything fails, you may want to show a inputbox with a default value, then user can change it accordingly and you can use the result with above SaveAs method.

I do some tests as soon as I'm in front a PC with Excel installed.