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

GTS

Board Regular
Joined
Aug 31, 2009
Messages
88
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.

VBA Code:
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,159
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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
 
Solution

OMGSALESFORCE2

New Member
Joined
Feb 24, 2021
Messages
34
Office Version
  1. 365
  2. 2019
  3. 2016
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.

VBA Code:
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

Board Regular
Joined
Aug 31, 2009
Messages
88
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,159
Office Version
  1. 365
Platform
  1. Windows
"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.
VBA Code:
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

Board Regular
Joined
Aug 31, 2009
Messages
88
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top