Loop thru and open only selected list of files in a folder

[GTS]

My searching has got me close, but not quite there. Most answers to my searches will loop thru ALL files in a folder. I only want to open certain files. **

One approach I'm trying is to extract the path and list of filenames from the folder. (Note - I am not dealing with subfolders... thankfully!)
I have the extraction working. My worksheet has the path in cell A1. The list of filenames starts at A2 and goes down the column. I can manually edit this list to remove unwanted files.

A1 - https:/ /mycomp.sharepoint.com/Shared Docs/Dept/Project/Customer/Est Rev 2/ (just an example... purposely did / /)
A2 - 1234 - Pump.xlsm
A3 - 1234 - Fan.xlsm
A4 - 1234 - Base.xlsm
A5 - SU1234 - Support
etc

Here is some simple code I'm working on.

Sub WBOpenTest1()

    Dim strPath As String, strPrj As String, strFilename As String
    Dim wbkCurr As Workbook
        
    strPath = Range("PATH_FOLDER") 'path to the folder of interest - cell A1
    strFilename = Range("FILENAME01") 'Single cell ok (eg A2).  Range (A2:A5) doesn't work.  Array?

    Workbooks.Open Filename:=(strPath & strFilename) 'Want to loop thru list of files and update them.  Inside a loop.

End Sub

It works if FILENAME01 is just a single cell (single file). I'm trying to get this to work with a list of files.

** As I said at the top, I'm trying to work with just certain files. When we were still on our own servers, I had this all working without extracting a list (using Dir). The filenames of interest always have the same number in them ("1234", but not always at the start) and are Excel files. I filtered for this directly in the code. Now I'm having to learn the ins and outs of SharePoint and I'm struggling.

Thanks.

 

[Kenneth Hobson]

Sub WBOpenTest1()
  Dim strPath As String, strPrj As String, rFilename As Range
  Dim wbkCurr As Workbook, r As Range
      
  strPath = Range("PATH_FOLDER") 'path to the folder of interest - cell A1
  Set rFilename = Range("FILENAME01") 'Single cell ok (eg A2).  Range (A2:A5) doesn't work.  Array?
  
  For Each r In rFilename
    Workbooks.Open Filename:=(strPath & r) 'Want to loop thru list of files and update them.  Inside a loop.
  Next r
End Sub

 

[OMGSALESFORCE2]

My searching has got me close, but not quite there. Most answers to my searches will loop thru ALL files in a folder. I only want to open certain files. **

One approach I'm trying is to extract the path and list of filenames from the folder. (Note - I am not dealing with subfolders... thankfully!)
I have the extraction working. My worksheet has the path in cell A1. The list of filenames starts at A2 and goes down the column. I can manually edit this list to remove unwanted files.

A1 - https:/ /mycomp.sharepoint.com/Shared Docs/Dept/Project/Customer/Est Rev 2/ (just an example... purposely did / /)
A2 - 1234 - Pump.xlsm
A3 - 1234 - Fan.xlsm
A4 - 1234 - Base.xlsm
A5 - SU1234 - Support
etc

Here is some simple code I'm working on.

Sub WBOpenTest1()

    Dim strPath As String, strPrj As String, strFilename As String
    Dim wbkCurr As Workbook
       
    strPath = Range("PATH_FOLDER") 'path to the folder of interest - cell A1
    strFilename = Range("FILENAME01") 'Single cell ok (eg A2).  Range (A2:A5) doesn't work.  Array?

    Workbooks.Open Filename:=(strPath & strFilename) 'Want to loop thru list of files and update them.  Inside a loop.

End Sub

It works if FILENAME01 is just a single cell (single file). I'm trying to get this to work with a list of files.

** As I said at the top, I'm trying to work with just certain files. When we were still on our own servers, I had this all working without extracting a list (using Dir). The filenames of interest always have the same number in them ("1234", but not always at the start) and are Excel files. I filtered for this directly in the code. Now I'm having to learn the ins and outs of SharePoint and I'm struggling.

Thanks.

The above video helps. There is code on the associated website that opens whichever folder you select based on the Search formula.

 

[GTS]

Kenneth - Thank you. That provided me with the looping action that I needed. I was able to use that to get to a working solution.

Omgsalesforce2 - The macro presented in the video is similar to what I had as a working solution when we had our own servers. To my understanding, DIR doesn't work with SharePoint files / paths. I'd already found a solution to getting a list of files from a SharePoint folder.

While I have things working, it is not quite as slick as before. Determining the path and (filtered) list of files to act on and then acting on them was all done in a single macro. Now, I have to run one macro to get the path and list of files, then another macro to run thru opening, make changes, saving and closing. Still 100x better than performing manual updates, but a small step backwards in functionality and user-friendliness.

Once again, thank you.

 

[Kenneth Hobson]

"This is the way" that I normally code things. I like my routines modular so that I can reuse them.

Had I wanted a list of files from sharepoint, I would use a WScript method to get all files. As for filter, that method has some options for that too. Of course I usually return an array from the WScript method where Filter() can be used on it to filter that way too. e.g.

Sub test_aFFs1()
  Dim x() As Variant
  
  x() = aFFs("x:\t\")
  MsgBox Join(x(), vbLf)
  MsgBox x(0), vbInformation, "First File"
  MsgBox x(1), vbInformation, "Second File"
  
  x() = aFFs("x:\t*", "/ad")  'Search for folders in x:\ that start with the letter "t".
  MsgBox Join(x(), vbLf)
  
  x() = aFFs("x:\t*", "/ad", True) 'Search for subfolders in x:\ that start with the letter "t".
  MsgBox Join(x(), vbLf)
End Sub

Sub Test_aFFs()
  Dim x, i As Long
  x = aFFs("C:\Users\ken\Dropbox\Excel\FileFolder\*.xlsm", , True)
  
  'MsgBox Join(x, vbLf)
  For i = 0 To UBound(x)
    Debug.Print i, x(i)
  Next i
End Sub

'Set extraSwitches, e.g. "/ad", to search folders only.
'MyDir should end in a "\" character unless searching by wildcards, e.g. "x:\test\t*
'Command line switches for the shell's Dir, http://ss64.com/nt/dir.html
Function aFFs(myDir As String, Optional extraSwitches = "", _
  Optional tfSubFolders As Boolean = False) As Variant
  
  Dim s As String, p As String, a() As String, v As Variant
  Dim b() As Variant, i As Long, fso As Object
  
  If tfSubFolders Then
    s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b /s " & extraSwitches).StdOut.ReadAll
    Else
    Set fso = CreateObject("Scripting.FileSystemObject")
    p = fso.GetParentFolderName(myDir) & "\"
    s = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b " & extraSwitches).StdOut.ReadAll
  End If
  
  a() = Split(s, vbCrLf)
  If UBound(a) = -1 Then
    MsgBox myDir & " not found.", vbCritical, "Macro Ending"
    Exit Function
  End If
  ReDim Preserve a(0 To UBound(a) - 1) As String 'Trim trailing vblfcr
  
  For i = 0 To UBound(a)
    If Not tfSubFolders Then
      'add the folder name
      a(i) = s & a(i)
    End If
  Next i
  
  Set fso = Nothing
  aFFs = sA1dtovA1d(a)
End Function

Function sA1dtovA1d(strArray() As String) As Variant
  Dim varArray() As Variant, i As Long
  ReDim varArray(LBound(strArray) To UBound(strArray))
  For i = LBound(strArray) To UBound(strArray)
    varArray(i) = CVar(strArray(i))
  Next i
  sA1dtovA1d = varArray()
End Function

 

[GTS]

Kenneth,

It would take me some time to go thru your code. My first reaction is that is over my head. My vba skills are rudimentary.

What I used to obtain the list of files is here >> VBA code to download List of files and folders from sharepoint
I did come across this same code on this forum as well. I'm a bit concerned about the drive mapping part of it, but it works and got me to a solution. Probably not the best or the most elegant, but at least it's a solution. Thank you.