Run VBA code on SharePoint files

[Prakash M]

Hello,

I'm using below VBA code to Copy data from 2 different excel files and paste in a Master file. Currently the code runs when we have all the 3 files in a folder and when the folder is open.

Below are the columns data which gets copied from 2 excel file to the Master file:
"State", "Region", "Location", "Status", "Date", "Name"

But my requirement is to run this macro where the files are stored to SharePoint.

Option Explicit

Sub Macro8()
Dim LObj As ListObject, mW, mS, j%, mCol, mRow, Rng As Range, fDate$
Application.ScreenUpdating = False
'------------------->
Set LObj = ActiveSheet.ListObjects(1)
With LObj
  If .ListRows.Count > 0 Then .DataBodyRange.Delete
  .ListRows.Add
End With
mW = Array("Category.xlsx", "Commodity.xlsx")   '-------> This are the 2 master trackers
mS = Array("CAT_Tracker", "COM_Tracker")        '-------> This are the 2 tabs in above 2 trackers
'------------------->
For j = 0 To UBound(mW)
  With Workbooks.Open(ThisWorkbook.Path & "\" & mW(j))
    Set Rng = .Sheets(mS(j)).[a1].CurrentRegion
    mCol = columnPosition(Rng.Worksheet, Array("State", "Region", "Location", "Status", "Date", "Name"))
    mRow = Evaluate("Row(2:" & Rng.Rows.Count & ")")
    With LObj.ListRows(LObj.ListRows.Count).Range.Resize(UBound(mRow))
      .Resize(, 1 + UBound(mCol)) = Application.Index(Rng, mRow, mCol)
      .Columns("L") = mW(j)
    End With
    .Close False
  End With
  LObj.ListRows.Add
Next
'------------------->
LObj.ListRows(LObj.ListRows.Count).Delete
fDate = Choose(1 + Application.International(xlDateOrder), "d/m/yyyy", "dd/mmm/yyyy", "yyyy/mm/dd")
LObj.ListColumns(3).DataBodyRange.NumberFormat = fDate
End Sub

Private Function columnPosition(cSh As Worksheet, cArray)
Dim i, k, C As Range
For i = 0 To UBound(cArray)
  Set C = cSh.Rows("1:1").Find(cArray(i), LookAt:=xlWhole)
  If C Is Nothing Then
    cSh.Parent.Close False
    Application.ScreenUpdating = True
    MsgBox "Column '" & cArray(i) & "' not found in workbook '" & cSh.Parent.Name & "'."
    End
  End If
  cArray(i) = C.Column
Next
columnPosition = cArray
End Function

Please can someone help to amend the above code to include SharePoint location. All the 3 files will be stored to SharePoint online.

 

[Marc L]

Hello, see with your IT first if it is doable on SharePoint online (often not).​

In case it's yes so your IT will give you the path to work with, in order you can fit it in 'your' VBA procedure …​

​