CODEWITHSUNDEEP
excelvba
piccolomair
piccolomair

Reputation: 25

Excel VBA: Designate Variable as Folderspec

I'm having trouble with this, hopefully minor issue and I cant seem to figure out how to solve it.

I have a vba script that works well in merging multiple workbook/worksheets into 1. However, the path of the folder is a static path. I'd like to make it a variable that can be defined by a filedialog function.

Here is the existing code for the file merger:

Sub ProjectMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False

Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.GetFolder("C:\Users\testUser\Desktop\FolderTest")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

Here is the script to use filedialog:

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 = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

I would think it would be easy enough to replace the folder path string with sItem to get this to work, but doing so always results in errors. Do i have to designate sItem as something else in order use it as the path? I'm not very familiar how Functions work vs regular subs.

Upvotes: 1

Views: 569

Answers (1)

Alex K.
Alex K.

Reputation: 175816

You simply need to capture the selected path returned from the function & deal with the possibility of it being empty if the user cancelled the dialog, E.g.

Dim path As String
path = GetFolder()

If (path <> "") Then
    Set dirObj = mergeObj.GetFolder(path)
    ....
    ....
Else
    '// user cancelled
End If

Upvotes: 2

Related Questions