0

I need to search for a folder based on Mgr = Data(I,2) value and Last = Data(I,1) value variables. I have all of the folders created in a certain pathway. How would I edit this:

wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
          ValidFileName(Mgr & "_" & Last & "_General Assessment.xlsx")

so that it searches a folder pathway for the Mgr variable (manager name) and then the Last variable (employee name) and then save the file in there? I don't want it to save in the folder pathway that the macro-enabled wb is saved, but to look for the appropriate folder in another pathway.

Rest of code:

  For i = 1 To UBound(Data)
    If Data(i, 1) <> Last Then
      If i > 1 Then
        Dest.Select
        wb.SaveCopyAs ThisWorkbook.Path & Application.PathSeparator & _
          ValidFileName(Mgr & "_" & Last & "_General Assessment.xlsx")

      End If
      Dest.Resize(, Columns.Count - Dest.Column).EntireColumn.ClearContents
      Last = Data(i, 1)
      Mgr = Data(i, 2)
      j = 0
    End If
    a = 0
    For k = 1 To UBound(Data, 2)
      Dest.Offset(a, j) = Data(i, k)
      a = a + 1
    Next
    j = j + 1
  Next

   SaveCopy wb, Last, Mgr '<< save the last report
nick lanta
  • 602
  • 3
  • 10
  • Just check it [with a FileSystemObject](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/folderexists-method). Same way [you would for a file](https://stackoverflow.com/a/14965606/4088852), just skipping the part where you enumerate the files. – Comintern Feb 11 '19 at 17:05
  • @Comintern guess I worded my question poorly. I know all of the folders > subfolders exist, I just don't know the syntax to have it search for the right folder to save under. I couldn't really get how to apply the aforementioned url to my question – nick lanta Feb 11 '19 at 17:08
  • If the structure exists, then the code you're looking for is [in the second link](https://stackoverflow.com/questions/14950475/recursively-access-subfolder-files-inside-a-folder?noredirect=1&lq=1). Just skip the part that looks at files. There are tons of questions that show how to do this, i.e. [this one](https://stackoverflow.com/q/20687810/4088852). Or am I missing what you're saying in "searches a folder pathway"? If you know the folder structure already exists, just build the path and use it. – Comintern Feb 11 '19 at 17:21
  • @Comintern So would I do something like `wb.SaveCopyAs TraverseFolders, ValidFileName(Mgr & "_" & Last & "_General Assessment.xlsx")` and just paste the `TraverseFolders` function at the bottom of that macro? – nick lanta Feb 11 '19 at 17:46
  • Do you know the path, or do you not know the path? I'm still not clear on how "need to search for a folder" and "I know all of the folders > subfolders exist" are consistent with each other. Are you trying to map non-path values to paths? – Comintern Feb 11 '19 at 17:51
  • @Comintern ok so say the subfolders are located here `C:\Users\Desktop\IA_Cuts\General`. the `Mgr` folders are located there, and the `Last` folders are located within those `Mgr` folders. How would I change `wb.SaveCopyAs TraverseFolders, ValidFileName(Mgr & "_" & Last & "_General Assessment.xlsx")` in order for each file cut to be saved within that `C:\Users\Desktop\IA_Cuts\General` folder pathway? – nick lanta Feb 11 '19 at 18:02
  • Just change `ThisWorkbook.Path` to the path it needs to be saved to. – Comintern Feb 11 '19 at 18:05
  • @Comintern but I have like 300 `Mgr` folders with up to 20 `Last` subfolders in that overarching path. If I just put the `C:\Users\Desktop\IA_Cuts\General\` path instead of `ThisWorkbook.Path` then will it search for those sub folders based on the `Mgr`>`Last` values in the current report that's being saved off? – nick lanta Feb 11 '19 at 18:08

0 Answers0