vba 如何浏览保存目录？

How to browse for save directory?

提问by Maz

By clicking a button in Excel, the user exports a specific sheet to a csv with a dynamic filename and the csv is saved in a pre-determined directory.

通过单击 Excel 中的按钮，用户可以将特定工作表导出为具有动态文件名的 csv，并且 csv 将保存在预先确定的目录中。

Instead of saving to a predetermined directory, can users have the browse window to choose a directory to save to?

用户是否可以使用浏览窗口来选择要保存到的目录，而不是保存到预定目录？

Sub Export()
Dim MyPath As String
Dim MyFileName As String
MyPath = "C:\importtest"

MyFileName = "MR_Update_" & Sheets("Monthly Review").Range("D3").Value & "_" & Format(Date, "ddmmyyyy")

If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"

Sheets("Export Data").Copy

With ActiveWorkbook

    .SaveAs Filename:= _
      MyPath & MyFileName, _
      FileFormat:=xlCSV, _
      CreateBackup:=False

    .Close False

End With
End Sub

采纳答案by ARich

As Patrick suggested, you're looking for the .FileDialogproperty.

正如帕特里克建议的那样，您正在寻找该.FileDialog物业。

To implement it, try this:

要实现它，试试这个：

Sub Export()
Dim MyPath As String
Dim MyFileName As String

MyFileName = "MR_Update_" & Sheets("Monthly Review").Range("D3").Value & "_" & Format(Date, "ddmmyyyy")

If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"

Sheets("Export Data").Copy

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = "" '<~~ The start folder path for the file picker.
    If .Show <> -1 Then GoTo NextCode
    MyPath = .SelectedItems(1) & "\"
End With

NextCode:

With ActiveWorkbook
    .SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV,CreateBackup:=False
    .Close False
End With
End Sub

回答by Siddharth Rout

Excel has an inbuilt FileSave Dialog. It is called .GetSaveAsFilename. Use that.

Excel 有一个内置的FileSave Dialog。它被称为.GetSaveAsFilename。用那个。

Syntax

句法

expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

Usage

用法

Dim fileSaveName As Variant

fileSaveName = Application.GetSaveAsFilename( _
                                    fileFilter:="Excel Files (*.csv), *.csv")
If fileSaveName <> False Then
    '
    '~~> Your code to save the file here
    '
End If

回答by CrashOverride

Try This......

尝试这个......

Sub Export()
Dim MyPath As String
Dim MyFileName As String
MyPath = "C:\importtest"

MyFileName = "MR_Update_" & Sheets("Monthly Review").Range("D3").Value & "_" & Format(Date, "ddmmyyyy")

If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"

Sheets("Export Data").Copy

With ActiveWorkbook

    .SaveAs Filename:= _
        MyFileName, _
        FileFormat:=xlCSV, _
        CreateBackup:=False

    .Close False
End With
End Sub

回答by peter.domanico

Here's a script I've been using lately that I like a lot. Thought I would leave this here:

这是我最近一直在使用的一个我非常喜欢的脚本。以为我会把这个留在这里：

Sub ExportCSV()

        Dim FlSv As Variant
        Dim MyFile As String
        Dim sh As Worksheet
        Dim MyFileName As String
        Dim DateString As String

        DateString = Format(Now(), "yyyy-mm-dd_hh_mm_ss_AM/PM") '<~~ uses current time from computer clock down to the second
        MyFileName = DateString & "_" & "Whatever you like"

        Set sh = Sheets("Sheet you'd like to export")
        sh.Copy
        FlSv = Application.GetSaveAsFilename(MyFileName, fileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Where should we save this?")

     If FlSv = False Then GoTo UserCancel Else GoTo UserOK

    UserCancel:         '<~~ this code is run if the user cancels out the file save dialog
        ActiveWorkbook.Close (False)
        MsgBox "Export canceled"
        Exit Sub

    UserOK:             '<~~ this code is run if user proceeds with saving the file (clicks the OK button)
        MyFile = FlSv
        With ActiveWorkbook
            .SaveAs (MyFile), FileFormat:=xlCSV, CreateBackup:=False
            .Close False
        End With


    End Sub