Excel VBA Saving File in Designated Location

Question

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!

See SaveAs won't accpet strings that contain “.” in Excel VBA[](stackoverflow.com/questions/36320580/…). — user4039065
– user4039065, Commented Apr 15, 2016 at 1:01

Jing Yi · Accepted Answer · 2016-04-15 10:45:15Z

1

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!

answered Apr 15, 2016 at 10:45

Jing Yi

2034 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JohnDunsmuir · Accepted Answer · 2021-01-12 11:38:17Z

0

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.

answered Jan 12, 2021 at 11:38

JohnDunsmuir

1666 bronze badges

Collectives™ on Stack Overflow

Excel VBA Saving File in Designated Location

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related