Excel VBA将文件保存在指定位置

问题描述：

以前我问过如何使用XLDialogaveAs（它适用于尚未保存的文件）将Excel文件保存到指定位置的问题 - Excel VBA XLDialogSaveAs function not working。但是，我正在尝试为已经保存在计算机中的Excel文件执行相同的操作，但改为改变位置。Excel VBA将文件保存在指定位置

下面我有以下代码：

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

有谁知道它擅长VBA功能我可以用更改Excel文件的保存位置，并在保存之前显示在对话框中指定的位置？谢谢！

请参阅SaveAs不会在Excel VBA []中包含“。”的字符串（http://stackoverflow.com/questions/36320580/saveas-wont-accpet-strings-that-contain-in-excel-vba/36320966＃36320966）。 – Jeeped

谢谢！ @Jeeped – JJ2015

答

我设法用下面的代码解决了这个问题。

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

谢谢！

Excel VBA将文件保存在指定位置

相关推荐