CODEWITHSUNDEEP
vbaexcel
Clauric
Clauric

Reputation: 1886

Select folder for save location

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.

Is this possible?

Edit 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.

Upvotes: 2

Views: 64090

Answers (2)

psychicebola
psychicebola

Reputation: 949

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

Upvotes: 9

sb.olofsson
sb.olofsson

Reputation: 919

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 vbNullString if the user cancelled the dialog.

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

Upvotes: 2

Related Questions