Open SharePoint file with part of filename

Question

How do I open a SharePoint file using part of the filename?

I have a file that is updated every year (Demand 2016, Demand 2017 etc.)

I currently link the file with the SharePoint url.

Set wb = ThisWorkbook
Set demandWB = Workbooks.Open("http INSERT URL Demand%20Plan%2016.xlsm")

I want to find the file with "Demand" in the name.

how about `If Filename Like "http INSERT URL Demand*" Then` , and the line below open it ? — Shai Rado, Dec 13 '16 at 14:59
Fair question, It was a typo, its supposed to reference another workbook in another public function — Jaz, Dec 13 '16 at 14:59
It's also not clear what you mean by "open a part of a filename" or "I want to find the file titled..." Please elaborate. — David Zemens, Dec 13 '16 at 14:59
[This answer](http://stackoverflow.com/a/1344957/2877364) talks about listing the contents of a SharePoint directory using Excel VBA - you could do that and then grab the filename that matched the pattern you wanted, e.g., `Left(filename,7)="Demand "`. — cxw, Dec 13 '16 at 15:00
@DavidZemens If I have a sharepoint file called "Demand 2016", I want to tell my code to go into root directory of SharePoint, and find me file with the title like "Demand"....ignore the year value. — Jaz, Dec 13 '16 at 15:01
You need to know the root directory, then use the answer that @cxw linked to above :) — David Zemens, Dec 13 '16 at 15:06
@cxw This method may work, running some tests now. appreciate the link — Jaz, Dec 13 '16 at 15:06
Thank you all! I got it working! I'll post the solution below :) Advise if my code is wrong as this works for me — Jaz, Dec 13 '16 at 15:19

score 2 · Answer 1 · edited Dec 13 '16 at 15:25

With the help of the comments above, I found a solution that worked. Below is the code to cycle through filenames in a SharePoint root directory and open a file that is similar to a string name

Public Sub ListFiles()
    Dim folder As folder
    Dim f As File
    Dim fs As New FileSystemObject
    Dim demandwb As Workbook

    Set folder = fs.GetFolder("//(SHAREPOINT URL)/GRS Demand Plan")

    For Each f In folder.Files
       If f.Name Like "GRS Demand Plan*" Then
           Set demandwb = Workbooks.Open(f)
       End If
    Next f
End Sub

Open SharePoint file with part of filename

1 Answers1