vba 选择文件夹作为保存位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33411063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select folder for save location
提问by Clauric
I have a VBA macro with about 20 modules, which create separate spreadsheets in the workbook. They also save the individual spreadsheet created by each module of the macro into a specific folder on a shared drive.
我有一个包含大约 20 个模块的 VBA 宏，它们在工作簿中创建单独的电子表格。他们还将宏的每个模块创建的单个电子表格保存到共享驱动器上的特定文件夹中。
This is an example of a couple of lines that save the spreadsheet to the separate folder.
这是将电子表格保存到单独文件夹的几行示例。
z = Format(DateTime.Now, "dd-MM-YYYY hhmm")
wb.SaveAs "J:\AAAA\BBBB\CCCC\DDDD\mod1" & z & ".xlsx"
Workbooks("mod1" & z & ".xlsx").Close savechanges:=True
However, as this file is now being shared out among a number of users, with different functions, the users now want to be able to set the location where the spreadsheets generated will be saved, on an individual basis.
但是，由于该文件现在在许多用户之间共享，具有不同的功能，因此用户现在希望能够单独设置生成的电子表格的保存位置。
What I am looking for is some way for the macro to open a new window, and for the user to select a file path. That file path would then be stored into the macro so that each module can read the file location where it needs to be stored.
我正在寻找的是宏打开新窗口以及用户选择文件路径的某种方式。然后将该文件路径存储到宏中，以便每个模块都可以读取需要存储的文件位置。
Is this possible?
这可能吗？
Edit 1:
编辑1：
I should have made a couple of things clearer. My apologies.
我应该把一些事情说得更清楚。我很抱歉。
The code above is replicated in every module. Also, all the modules are run from one overarching module, that calls the other.
上面的代码被复制到每个模块中。此外，所有模块都从一个总体模块运行，该模块调用另一个模块。
What I am looking for is a code that will allow the user to select the save location at the start of the overarching module. Eg. J\AAA\CCC\XXX. The modules, when called, will to retrieve the file path, and then save the file to that location.
我正在寻找的是允许用户在总体模块开始时选择保存位置的代码。例如。J\AAA\CCC\XXX。模块在调用时将检索文件路径，然后将文件保存到该位置。
回答by psychicebola
use this function:
使用这个功能：
Function GetFolder() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
'.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
it returns a folderpath
它返回一个文件夹路径
回答by sb.olofsson
If you want to have them select a file name, you can use this function. It prompts the user for a folder location and file name. The function returns an absolute file path. It returns vbNullStringif the user cancelled the dialog.
如果你想让他们选择一个文件名，你可以使用这个功能。它会提示用户输入文件夹位置和文件名。该函数返回一个绝对文件路径。vbNullString如果用户取消对话框，它会返回。
Public Function SaveWorkbook() As String
Dim fileName As Variant
fileName = Application.GetSaveAsFilename(fileFilter:="Excel Workbook (*.xlsx), *.xlsx")
If fileName <> False Then Exit SaveWorkbook = fileName
End Sub
