CODEWITHSUNDEEP
vbaexcel
Jing Yi
Jing Yi

Reputation: 203

Excel VBA Saving File in Designated Location

previously I asked a question on how to save an Excel file to an designated location using XLDialogaveAs (which works for files that have yet to be saved) - Excel VBA XLDialogSaveAs function not working. However, I am trying to do the same thing for an Excel file that have already been saved in the computer, but change the location instead.

I have the following codes below: 

Option Explicit

Sub externalRatingChangeFile()

    'Declare the data type of the variables
    Dim wks As Worksheet
    Dim sFilename As String

    'Set wks to the current active worksheet
    Set wks = ActiveWorkbook.ActiveSheet

    'Set the location to save the file to a variable
    sFilename = "H:\testing file"

    'Save as .xlsx file in the specific location stated earlier
    'If there are errors in the code, set wks to nothing and end the process
    On Error GoTo err_handler
    ChDrive sFilename
    ChDir sFilename
    Application.Dialogs(xlDialogSaveAs).Show (sFilename & "\TestingFile - " & Format(Date, "YYYYMMDD") & ".xlsx")

    'System to/not display alerts to notify Users that they are replacing an existing file.
    Application.DisplayAlerts = True

    err_handler:
    'Set Wks to its default value
    Set wks = Nothing

End Sub

Does anyone know which excel VBA function can I use to change the saving location of the Excel file, and show the designated location in the dialog box before saving? Thanks!

Upvotes: 1

Views: 913

Answers (2)

JohnDunsmuir
JohnDunsmuir

Reputation: 166

I had the same issue with FileDialog(msoFileDialogOpen), I also noticed that .InitialFileName persists in subsequent calls if not reset to InitialFileName = "" (Excel 2013)

Application.GetOpenFileName changes the target directory correctly but requires special handling of the return value. When MultiSelect = False it returns as string containing the file path or "False" if cancelled. When MultiSelect = True it returns a variant with a list of the selected file paths or Boolean = False when cancelled.

Upvotes: 0

Jing Yi
Jing Yi

Reputation: 203

I managed to solve this issue with the code below. 

Set fdlg = Application.FileDialog(msoFileDialogSaveAs)
With fdlg
    .InitialFileName = sFilename
    .Show

'If there are errors in the code, set wks to nothing and end the process
On Error GoTo err_handler
    wks.SaveAs (fdlg.SelectedItems(1))
End With

Thanks!

Upvotes: 1

Related Questions