title: Excel VBA: Designate Variable as Folderspec
body: <p>I'm having trouble with this, hopefully minor issue and I cant seem to figure out how to solve it.</p>
  
  <p>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.</p>
  
  <p>Here is the existing code for the file merger:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Here is the script to use filedialog:</p>
  
  <pre><code>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 &lt;&gt; -1 Then GoTo NextCode
          sItem = .SelectedItems(1)
      End With
  NextCode:
      GetFolder = sItem
      Set fldr = Nothing
  End Function
  </code></pre>
  
  <p>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.</p>