选择文件夹以保存位置 [英] Select folder for save location

问题描述

我有一个带有约20个模块的VBA宏，它们在工作簿中创建单独的电子表格.它们还将由宏的每个模块创建的单个电子表格保存到共享驱动器上的特定文件夹中.

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.

这是将电子表格保存到单独文件夹的几行示例.

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.

这可能吗?

我本应该更清楚地说明几件事.我很抱歉.

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.

我要寻找的是一个允许用户在总体模块开始时选择保存位置的代码.例如. J \ AAA \ CCC \ XXX.这些模块在被调用时将检索文件路径，然后将文件保存到该位置.

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.

推荐答案

使用此功能:

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屋！