title: Excel Files on Sharepoint
body: <p>I have a question regarding Excel VBA. I have multiple excel files located in a sharepoint folder. I need a macro that will combine these files into one document and has refresh capabilities. I have a code that works great when the excel files are on my D: drive, however I am not sure how to change my code if the files are now in a file on sharepoint.  The code has an error at the File Name stage. Does anyone know how to change my code below if the folder path is now a url?Any help would be great! Thanks so much!!!</p>
  
  <pre><code>    Sub copyOrRefreshSheet(destWb As Workbook, sourceWs As Worksheet)
        Dim ws As Worksheet
        On Error Resume Next
        Set ws = destWb.Worksheets(sourceWs.Name)
        On Error GoTo 0
        If ws Is Nothing Then
          sourceWs.Copy After:=destWb.Worksheets(destWb.Worksheets.Count)
        Else
          ws.Cells.ClearContents
          ws.Range(sourceWs.UsedRange.Address).Value = sourceWs.UsedRange.Value2
        End If
      End Sub
  
  Sub ConslidateWorkbooks()
  
  Dim FolderPath As String
  Dim Filename As String
  Dim Sheet As Worksheet
  
  Application.ScreenUpdating = False
  FolderPath = "D:\Users\jfreds\Desktop\temp folder\"  
  Filename = Dir(FolderPath&amp; "*.xls*")                                                             
  
  Do While Filename &lt;&gt; ""
  Workbooks.Open Filename:=FolderPath &amp; Filename, ReadOnly:=True
  For Each Sheet In ActiveWorkbook.Sheets
  copyOrRefreshSheet ThisWorkbook, Sheet
  Next Sheet
  Workbooks(Filename).Close
  Filename = Dir()
  Loop
  
  End Sub
  </code></pre>